package com.cindata

import com.cindata.dictionary.*
import com.cindata.dimension.RiskDimension
import com.cindata.dimension.YearLimitDimension
import grails.converters.JSON
import grails.transaction.Transactional
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.formula.functions.T
import org.springframework.security.access.annotation.Secured

import java.text.SimpleDateFormat

@Secured(['isAuthenticated()'])
@Transactional
class AnalyzeController
{
    def dataSource
    def springSecurityService
    def apiService

    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]

    def index()
    {

    }

    def index1()
    {

    }

    def analazySeach()
    {

        respond(view: "analazySeach")
    }

    /**
     * 区域分析*/
    def area()
    {
        respond view: 'area'
    }

    def area1()
    {
        respond view: 'area1'
    }

    /**
     * 区域分析数据请求(机构过滤)
     * @return
     */
    def areaData()
    {
        def city = params["city"]
        def type = params["type"]
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]
        def citySql = "e.collateral.city.zoneNumber as zoneNumber,e.collateral.city.name as cityName "
        if (city && city != '-1')
        {
            citySql = "e.collateral.district.zoneNumber as zoneNumber,e.collateral.district.name as cityName  "
        }

        def sql = ""
        def formatSql = this.getSql(params)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            formatSql += " and e.collateral.account.id in ${accountSql}"
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateStart != '')
        {
            formatSql += " and e.mortgage.cltv*100 >= " + dynamicMortgageRateStart
        }
        if (dynamicMortgageRateEnd && dynamicMortgageRateEnd != '')
        {
            formatSql += " and e.mortgage.cltv*100 <= " + dynamicMortgageRateEnd
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    formatSql += " and e.mortgage.loansDuration > ${it.minValue} and e.mortgage.loansDuration<= ${it.maxValue} "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    formatSql += " and e.mortgage.remainTime > ${it.minValue} and e.mortgage.remainTime<=${it.maxValue} "
                }
            }
        }

        if (city && city != '-1')
        {
            formatSql += " and e.collateral.city.zoneNumber='${params["city"]}' group by e.collateral.district.name,e.collateral.district.zoneNumber " + "order by value desc "
        }
        else
        {
            formatSql += " group by e.collateral.city.name,e.collateral.city.zoneNumber order by value desc"
        }
        def result = new HashMap()

        if (type == '1')
        {
            sql = "select new map(count(e.mortgage.id) as value," + citySql + " ) from MortgageCollateral e where e.collateral.estateStatus.code = '1' "
            sql += formatSql
            println sql
            def estateResult = Estate.executeQuery(sql).asList()
            println estateResult
            result.put("estateResult", estateResult)
        }
        else if (type == '2')
        {
            sql = "select new map(sum(e.mortgage.loansAmount) as value," + citySql + ") from MortgageCollateral e where e.collateral.estateStatus.code = '1' "
            sql += formatSql
            def loansAmountResult = Estate.executeQuery(sql).asList()
            result.put("loansAmountResult", loansAmountResult)
        }
        else if (type == '3')
        {
            sql = "select new map(sum(e.mortgage.loansBalance) as value," + citySql + ") from MortgageCollateral e  where e.collateral.estateStatus.code = '1'"
            sql += formatSql
            def loansBalanceResult = Estate.executeQuery(sql).asList()
            result.put("loansBalanceResult", loansBalanceResult)
        }
        else if (type == '4')
        {
            sql = "select new map(sum(e.mortgage.loansBalance)/sum(e.mortgage.totalPrice) as value," + citySql + " )  from  MortgageCollateral e " + "where e.collateral.estateStatus.code = '1' "
            sql += formatSql
            def ltvResult = Estate.executeQuery(sql).asList()
            result.put("ltvResult", ltvResult)
        }
        render([result: result] as JSON)
    }

    /**
     * 表单条件sql语句
     * @param params
     */
    def getSql(params)
    {
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款余额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //贷款金额上限
        def startDateStart = params["startDateStart"]
        //放款时间开始
        def startDateEnd = params["startDateEnd"]
        //放款时间结束
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //面积上限
        def endDateStart = params["endDateStart"]
        //贷款到期日开始
        def endDateEnd = params["endDateEnd"]
        //贷款到期日结束
        def risk = params["risk"]
        def sql = ""

        //        if (city && city != '-1')
        //        {
        //            sql += "and e.city.zoneNumber = '${city}' "
        //        }
        //行政区和城市在有的页面不存在 单独写判断
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and e.loansBalance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and e.loansBalance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and e.loansAmount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and e.loansAmount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.startDate ,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}' "
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "  and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}'  "
        }
        if (endDateStart)
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.endDate,'YYYY-MM-DD HH24:MI:SS')>='${dates1}'  "
        }
        if (endDateEnd)
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "  and  to_char(e.endDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e.estate.buildArea >= " + buildAreaStart
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e.estate.buildArea <= " + buildAreaEnd
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and e.cltv <=${maxValue} "
                }
            }
        }
        return sql
    }

    /**
     * 抵押率分布
     * @return
     */
    def mortgageRate()
    {

        respond(view: "mortgageRate")
    }

    def mortgageRate1()
    {

        respond(view: "mortgageRate1")
    }

    /**
     *  抵押率趋势数据（机构过滤）
     * @return
     */
    def mortgageRateData()
    {
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]

        def sql = "select new map(count(e.mortgage.id) as esateCount,sum(e.mortgage.loansBalance) as loansBalance ," + "sum(e.mortgage.loansAmount) as loansAmount,to_char(e.mortgage.startDate,'yyyy-mm') as startDate)" + " from MortgageCollateral e   where 1=1  "
        def formatSql = this.getSql(params)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            formatSql += " and e.collateral.account.id in ${accountSql}"
        }
        if (city && city != '-1')
        {
            formatSql += " and e.collateral.city.zoneNumber ='${city}' "
        }
        if (district && district != '-1')
        {
            formatSql += " and e.collateral.district.zoneNumber ='${district}' "
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateStart != '')
        {
            formatSql += " and e.mortgage.cltv*100 >=" + dynamicMortgageRateStart
        }
        if (dynamicMortgageRateEnd && dynamicMortgageRateEnd != '')
        {
            formatSql += " and e.mortgage.cltv*100 <= " + dynamicMortgageRateEnd
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    formatSql += " and e.mortgage.loansDuration > ${it.minValue} and e.mortgage.loansDuration<=${it.maxValue}  "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    formatSql += " and e.mortgage.remainTime > ${it.minValue}  and e.mortgage.remainTime<= ${it.maxValue}  "
                }
            }
        }

        formatSql += " group by to_char(e.mortgage.startDate,'yyyy-mm')  order by startDate "
        sql += formatSql
        def result = Estate.executeQuery(sql).asList()
        render([result: result] as JSON)
    }

    /**
     * 数量趋势分布
     * @return
     */
    def estateAnalyze()
    {

        respond(view: "estateAnalyze")
    }

    def estateAnalyze1()
    {

        respond(view: "estateAnalyze1")
    }

    /**
     * 数量趋势的数据（机构过滤）
     * @return
     */
    def estateData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款余额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //贷款金额上限
        def startDateStart = params["startDateStart"]
        //放款时间开始
        def startDateEnd = params["startDateEnd"]
        //放款时间结束
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //面积上限
        def endDateStart = params["endDateStart"]
        //贷款到期日开始
        def endDateEnd = params["endDateEnd"]
        def risk = params["risk"]
        def sql = "SELECT " + "  sum(tt.orderNumber) as orderNumber," + "  sum(tt.count) as count ," + "  sum(tt.loansbalance) as loansbalance," + "  sum(tt.loansAmount) as loansAmount," + "  sum(loansEvaluateTotalPrice) as loansEvaluateTotalPrice," + "  subsection " + "FROM ( "
        //        def sql = "SELECT " + "max(d.order_number) as orderNumber," + "d.label AS subsection, " + "       count(*) " +
        //                "as" + " count, " + "       sum(e.loans_balance) as loansBalance, " + "       sum(e.loans_amount) as " +
        //                "" + "loansAmount, " + "       sum(e.total_Price)     AS loansEvaluateTotalPrice " + "FROM estate e "
        //        + "right " + "join dimension d on e.cltv*100 > d.min_value  " + "and e.cltv*100 <= d.max_value and class='com"
        //        + ".cindata.dimension.LTVDimension' " + "left join area  a on e.city_id=a.id left join area  b on e.district_id =b.id where 1=1 "
        sql += "SELECT 0 as ordernumber, d.label AS subsection, CASE WHEN count(e.id) IS NULL THEN 0 ELSE count(e.id) END as count, sum(e.loans_balance) AS loansBalance, sum(e.loans_amount) AS loansAmount, sum(e.total_Price) AS loansEvaluateTotalPrice FROM Mortgage e INNER JOIN dimension d ON e.cltv * 100 >= d.min_value AND e.cltv * 100 < d.max_value AND " + " d.class = 'com.cindata.dimension.LTVDimension' " + " LEFT JOIN Estate e1 ON e.estate_id = e1.id   LEFT JOIN area a ON e1.city_id = a.area_id " + " LEFT JOIN area b ON e1.district_id = b.area_id where 1=1"
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e1.account_id in ${accountSql} "
        }
        sql += " and e1.estate_status_id = (select dt.id from Data_dict dt where dt.code = '1' and dt.class = 'com.cindata.dictionary.EstateStatusDict') "

        if (city && city != '-1')
        {
            sql += " and a.zone_Number ='${city}' "
        }
        if (district && district != '-1')
        {
            sql += " and b.zone_Number ='${district}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    sql += " and e.loans_Duration > ${it.minValue} and e.loans_Duration<= ${it.maxValue}  "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    sql += " and e.remain_Time > ${it.minValue}  and  e.remain_Time<=${it.maxValue}  "
                }
            }
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and e.loans_Balance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and e.loans_Balance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and e.loans_Amount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and e.loans_Amount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.start_date,'YYYY-MM-DD HH24:MI:SS')  >= '${dates1}' "
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.start_date,'YYYY-MM-DD HH24:MI:SS') <='${datee1}'"
        }
        if (endDateStart)
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.end_date,'YYYY-MM-DD HH24:MI:SS') >=  '${dates1}' "
        }
        if (endDateEnd)
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "  and to_char(e.end_date,'YYYY-MM-DD HH24:MI:SS')<='${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e1.build_Area >= " + buildAreaStart
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e1.build_Area <= " + buildAreaEnd
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateEnd)
        {
            def start = Integer.parseInt(dynamicMortgageRateStart.toString()) / 100
            def end = Integer.parseInt(dynamicMortgageRateEnd.toString()) / 100
            sql += " and e.cltv > ${start} AND e.cltv <= ${end}  "
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and e.cltv<=${maxValue} "
                }
            }
        }
        sql += " group by d.label "
        sql += " UNION " + "       SELECT" + "         d1.order_number as ordernumber," + "         d1.label,0,0,0,0 " + "       FROM dimension d1 " + "       WHERE d1.class = 'com.cindata.dimension.LTVDimension' " + "       GROUP BY d1.label,d1.order_number)  tt " + "GROUP BY tt.subsection"

        println sql
        def result = db.rows(sql).asList()
        result.each {
            if (it.loansEvaluateTotalPrice == 0 || it.loansBalance == 0)
            {
                it.cltv = 0
            }
            else
            {
                def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
                it.cltv = cltv
            }

        }
        render([result: result] as JSON)
    }

    /**
     * 押品数量Top10小区
     * @return
     */
    def mortgageCountCommunityTopTen()
    {
        respond(view: "mortgageCountCommunityTopTen")
    }

    /**
     * 押品数量/面积Top10小区数据请求 (机构过滤)
     * @return
     */
    def mortgageCountTopTenData()
    {
        def max = params['max']
        def type = params['type']
        def city = params['city']
        def district = params['district']
        def countSql = " select new map ( e.communityName as communityName, e.communityId as communityId , count(e.communityName) as value ) from Estate e where e.estateStatus.code = '1' "
        def areaSql = " select new map ( e.communityName as communityName, e.communityId as communityId , sum(case when e.sourceBuildArea is null then 0 else e.sourceBuildArea end) as value ) from Estate e where e.estateStatus.code = '1' "
        def sql = " "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.account.id in ${accountSql} "
        }
        if (city && city != '-1')
        {
            //按城市查询
            sql += " and e.city.zoneNumber = '${city}'"
        }
        if (district && district != '-1')
        {
            //按行政区查询
            sql += " and e.district.zoneNumber = '${district}'"

        }
        if (max)
        {
            params.max = max
        }
        else
        {
            params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        }
        params.offset = params.offset ? params.offset.toInteger() : 0

        sql += " group by e.communityName ,e.communityId  order by  value desc "
        def result = new HashMap()
        if (type == '1')
        {
            //押品数量
            countSql += sql
            def mortgageCountList = Estate.executeQuery(countSql, [max: params.max, offset: params.offset]).asList()
            result.put("mortgageCountList", mortgageCountList)
        }
        else if (type == '2')
        {
            //面积
            areaSql += sql
            def areaList = Estate.executeQuery(areaSql, [max: params.max, offset: params.offset]).asList()
            result.put("areaList", areaList)
        }
        render([result: result] as JSON)
    }

    /**
     * 动态抵押率Top10小区
     * @return
     */
    def mortgageRateCommunityTopTen()
    {
        respond(view: "mortgageRateCommunityTopTen")
    }

    /**
     * 动态抵押率Top10小区数据请求 (机构过滤)
     * @return
     */
    def mortgageRateTopTenData()
    {
        def max = params['max']
        def city = params['city']
        def district = params['district']
        def sql = " select new map ( e.communityName as communityName ,e.communityId as communityId , avg(case when e.cltv is null then 0 else e.cltv end )*100 as value ) from Estate e where e.estateStatus.code = '1' "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.account.id in ${accountSql} "
        }
        if (city && city != '-1')
        {
            //按城市查询
            sql += " and e.city.zoneNumber = '${city}'"
        }
        if (district && district != '-1')
        {
            //按行政区查询
            sql += " and e.district.zoneNumber = '${district}'"
        }
        if (max)
        {
            params.max = max
        }
        else
        {
            params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        }
        params.offset = params.offset ? params.offset.toInteger() : 0

        sql += "group by e.communityName ,e.communityId  order by value desc "
        def mortgageRateList = Estate.executeQuery(sql, [max: params.max, offset: params.offset]).asList()
        render([mortgageRateList: mortgageRateList] as JSON)
    }

    /**
     * 贷款余额Top10小区
     * @return
     */
    def loanBalanceCommunityTopTen()
    {
        respond(view: "loanBalanceCommunityTopTen")
    }

    /**
     * 贷款余额/贷款额Top10小区数据请求（机构过滤）
     * @return
     */
    def loanBalanceTopTenData()
    {
        def max = params['max']
        def type = params['type']
        def city = params['city']
        def district = params['district']
        def loanBalanceSql = " select new map ( e.communityName as communityName ,e.communityId as communityId ,sum(case when e.loansBalance is null then 0 else e.loansBalance end) as value ) from Estate e where e.estateStatus.code = '1' "
        def loanAmountSql = " select new map ( e.communityName as communityName ,e.communityId as communityId ,sum(case when e.loansAmount is null then 0 else e.loansAmount end) as value ) from Estate e where e.estateStatus.code = '1' "
        def sql = " "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.account.id in ${accountSql} "
        }
        if (city && city != '-1')
        {
            //按城市查询
            sql += " and e.city.zoneNumber = '${city}'"
        }
        if (district && district != '-1')
        {
            //按行政区查询
            sql += " and e.district.zoneNumber = '${district}'"

        }
        if (max)
        {
            params.max = max
        }
        else
        {
            params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        }
        params.offset = params.offset ? params.offset.toInteger() : 0
        sql += " group by e.communityName ,e.communityId order by  value desc "
        def result = new HashMap()
        if (type == '1')
        {
            //贷款余额
            loanBalanceSql += sql
            def loanBalanceList = Estate.executeQuery(loanBalanceSql, [max: params.max, offset: params.offset]).asList()
            result.put("loanBalanceList", loanBalanceList)
        }
        else if (type == '2')
        {
            //贷款金额
            loanAmountSql += sql
            def loanAmountList = Estate.executeQuery(loanAmountSql, [max: params.max, offset: params.offset]).asList()
            result.put("loanAmountList", loanAmountList)
        }

        render([result: result] as JSON)
    }

    /**
     * 贷款额分布
     * @return
     */

    @Transactional
    def loanAmountDistribution()
    {
        respond(view: "loanAmountDistribution")
    }

    def loanAmountDistribution1()
    {
        respond(view: "loanAmountDistribution1")
    }

    /**
     * 贷款额趋势分析数据（机构过滤）
     * @return
     */

    @Transactional
    def loanAmountDate()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款余额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //贷款金额上限
        def startDateStart = params["startDateStart"]
        //放款时间开始
        def startDateEnd = params["startDateEnd"]
        //放款时间结束
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //面积上限
        def endDateStart = params["endDateStart"]
        //贷款到期日开始
        def endDateEnd = params["endDateEnd"]
        //押品风险
        def risk = params["risk"]
        def sql = "  select sum(tt.orderNumber) as orderNumber,sum(tt.count) as count ,sum(tt.loansBalance) as loansBalance," + "  sum(tt.loansAmount) as loansAmount,sum(loansEvaluateTotalPrice) as loansEvaluateTotalPrice,subsection from ( "
        sql += "  select 0 as orderNumber,d.label  as subsection, case when count(e.id) is null then 0 else count(e.id) end as count, " + "  sum(e.loans_balance) as loansBalance, sum(e.loans_amount)  as loansAmount, sum(e.total_Price)   as loansEvaluateTotalPrice " + "  from mortgage e inner join dimension d on  e.loans_amount > d.min_value and e.loans_amount <= d.max_value  and " + "  d.class='com.cindata.dimension.AmountDimension' left join estate e1 on e.estate_id = e1.id left join area a on e1.city_id = a.area_id left join area b on e1.district_id = b.area_id where 1=1 "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e1.account_id in ${accountSql} "
        }
        sql += " and e1.estate_status_id = ( select dt.id from Data_dict dt where dt.code = '1' and dt.class = 'com.cindata.dictionary.EstateStatusDict') "
        if (city && city != '-1')
        {
            sql += " and a.zone_Number ='${city}' "
        }
        if (district && district != '-1')
        {
            sql += " and b.zone_Number ='${district}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    sql += " and e.loans_Duration > ${it.minValue}  and e.loans_Duration<= ${it.maxValue}  "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    sql += " and e.remain_Time > ${it.minValue}  and e.remain_Time <=${it.maxValue}  "
                }
            }
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and e.loans_Balance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and e.loans_Balance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and e.loans_Amount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and e.loans_Amount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart)
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD') >='${dates1}'  "
        }
        if (startDateEnd)
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD')<='${datee1}'"
        }
        if (endDateStart)
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.end_date,'YYYY-MM-DD') >= '${dates1}' "
        }
        if (endDateEnd)
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "   and to_char(e.end_date,'YYYY-MM-DD')<='${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e1.build_Area >= " + buildAreaStart
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e1.build_Area <= " + buildAreaEnd
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateEnd)
        {
            def start = Integer.parseInt(dynamicMortgageRateStart.toString()) / 100
            def end = Integer.parseInt(dynamicMortgageRateEnd.toString()) / 100
            sql += " and e.cltv > ${start} AND e.cltv <= ${end}  "
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and  e.cltv <= ${maxValue} "
                }
            }
        }
        sql += " group by d.label "
        sql += " union select d1.order_number as orderNumber,d1.label,0,0,0,0 from dimension d1 " + "  where d1.class='com.cindata.dimension.AmountDimension' " + "  group by d1.label,d1.order_number)  tt " + "  group by tt.subsection"

        def result = db.rows(sql).asList()
        result.each {
            if (it.loansEvaluateTotalPrice == 0 || it.loansBalance == 0)
            {
                it.ltv = 0
            }
            else
            {
                def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
                it.ltv = cltv
            }

        }

        render([result: result] as JSON)
    }

    /**
     * 贷款余额分布
     * @return
     */

    @Transactional
    def loanBalanceDistribution()
    {
        respond(view: "loanBalanceDistribution")
    }

    def loanBalanceDistribution1()
    {
        respond(view: "loanBalanceDistribution1")
    }

    /**
     *
     * 贷款余额趋势分析数据（机构过滤）
     * @return
     */

    @Transactional
    def loanBalanceDate()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款余额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //贷款金额上限
        def startDateStart = params["startDateStart"]
        //放款时间开始
        def startDateEnd = params["startDateEnd"]
        //放款时间结束
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //面积上限
        def endDateStart = params["endDateStart"]
        //贷款到期日开始
        def endDateEnd = params["endDateEnd"]
        //押品风险
        def risk = params["risk"]
        def sql = "  select sum(tt.orderNumber) as orderNumber,sum(tt.count) as count ,sum(tt.loansBalance) as loansBalance," + "  sum(tt.loansAmount) as loansAmount,sum(loansEvaluateTotalPrice) as loansEvaluateTotalPrice,subsection from ( "
        sql += "  select 0 as orderNumber,d.label  as subsection, case when count(e.id) is null then 0 else count(e.id) end as count, " + "  sum(e.loans_balance) as loansBalance, sum(e.loans_amount)  as loansAmount, sum(e.total_Price)   as loansEvaluateTotalPrice " + "  from mortgage e inner join dimension d on  e.loans_amount > d.min_value and e.loans_amount <= d.max_value  and " + "  d.class='com.cindata.dimension.AmountDimension' left join estate e1 on e.estate_id = e1.id left join area a on e1.city_id = a.area_id left join area b on e1.district_id = b.area_id where 1=1 "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e1.account_id in ${accountSql} "
        }
        sql += " and e1.estate_status_id = ( select dt.id from Data_dict dt where dt.code = '1' and dt.class = 'com.cindata.dictionary.EstateStatusDict') "
        if (city && city != '-1')
        {
            sql += " and a.zone_Number ='${city}' "
        }
        if (district && district != '-1')
        {
            sql += " and b.zone_Number ='${district}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    sql += " and e.loans_Duration > ${it.minValue}  and e.loans_Duration<= ${it.maxValue}  "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    sql += " and e.remain_Time > ${it.minValue}  and e.remain_Time <=${it.maxValue}  "
                }
            }
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and e.loans_Balance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and e.loans_Balance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and e.loans_Amount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and e.loans_Amount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart)
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD') >='${dates1}'  "
        }
        if (startDateEnd)
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD')<='${datee1}'"
        }
        if (endDateStart)
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.end_date,'YYYY-MM-DD') >= '${dates1}' "
        }
        if (endDateEnd)
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "   and to_char(e.end_date,'YYYY-MM-DD')<='${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e1.build_Area >= " + buildAreaStart
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e1.build_Area <= " + buildAreaEnd
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateEnd)
        {
            def start = Integer.parseInt(dynamicMortgageRateStart.toString()) / 100
            def end = Integer.parseInt(dynamicMortgageRateEnd.toString()) / 100
            sql += " and e.cltv > ${start} AND e.cltv <= ${end}  "
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and  e.cltv <= ${maxValue} "
                }
            }
        }
        sql += " group by d.label "
        sql += " union select d1.order_number as orderNumber,d1.label,0,0,0,0 from dimension d1 " + "  where d1.class='com.cindata.dimension.AmountDimension' " + "  group by d1.label,d1.order_number)  tt " + "  group by tt.subsection"
        def result = db.rows(sql).asList()
        result.each {
            if (it.loansEvaluateTotalPrice == 0 || it.loansBalance == 0)
            {
                it.ltv = 0
            }
            else
            {
                def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
                it.ltv = cltv
            }

        }
        render([result: result] as JSON)
    }

    /**
     * 贷款年限分布
     * @return
     */

    @Transactional
    def loanAgeDistribution()
    {
        respond(view: "loanAgeDistribution")
    }

    def loanAgeDistribution1()
    {
        respond(view: "loanAgeDistribution1")
    }

    /**
     * 贷款年限趋势分析数据（机构过滤）
     * @return
     */

    @Transactional
    def loanAgeDate()
    {
        def db = new groovy.sql.Sql(dataSource)
        def city = params['city']
        def district = params['district']
        def loansDuration = params["loansDuration"]
        //贷款时间
        def remainTime = params["remainTime"]
        //剩余贷款时长
        def dynamicMortgageRateStart = params["dynamicMortgageRateStart"]
        def dynamicMortgageRateEnd = params["dynamicMortgageRateEnd"]
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款余额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //贷款金额上限
        def startDateStart = params["startDateStart"]
        //放款时间开始
        def startDateEnd = params["startDateEnd"]
        //放款时间结束
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //面积上限
        def endDateStart = params["endDateStart"]
        //贷款到期日开始
        def endDateEnd = params["endDateEnd"]
        //押品风险
        def risk = params["risk"]
        def sql = "  select sum(tt.orderNumber) as orderNumber,sum(tt.count) as count ,sum(tt.loansBalance) as loansBalance," + "  sum(tt.loansAmount) as loansAmount,sum(loansEvaluateTotalPrice) as loansEvaluateTotalPrice,subsection from ( "
        sql += "  select 0 as orderNumber,d.label  as subsection, case when count(e.id) is null then 0 else count(e.id) end as count, " + "  sum(e.loans_balance) as loansBalance, sum(e.loans_amount)  as loansAmount, sum(e.total_Price)   as loansEvaluateTotalPrice " + "  from mortgage e inner join dimension d on  e.loans_duration > d.min_value and e.loans_duration <= d.max_value  and " + "  d.class='com.cindata.dimension.YearLimitDimension' left join estate e1 on e.estate_id = e1.id left join area a on e1.city_id = a.area_id left join area b on e1.district_id = b.area_id where 1=1 "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e1.account_id in ${accountSql} "
        }
        sql += " and e1.estate_status_id = ( select dt.id from Data_dict dt where dt.code = '1' and dt.class = 'com.cindata.dictionary.EstateStatusDict') "
        if (city && city != '-1')
        {
            sql += " and a.zone_Number ='${city}' "
        }
        if (district && district != '-1')
        {
            sql += " and b.zone_Number ='${district}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    sql += " and e.loans_Duration > ${it.minValue}  and e.loans_Duration<= ${it.maxValue}  "
                }
            }
        }
        if (remainTime && remainTime != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == remainTime)
                {
                    sql += " and e.remain_Time > ${it.minValue}  and e.remain_Time <=${it.maxValue}  "
                }
            }
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and e.loans_Balance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and e.loans_Balance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and e.loans_Amount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and e.loans_Amount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart)
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD') >='${dates1}'  "
        }
        if (startDateEnd)
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.start_date, 'YYYY-MM-DD')<='${datee1}'"
        }
        if (endDateStart)
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(e.end_date,'YYYY-MM-DD') >= '${dates1}' "
        }
        if (endDateEnd)
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "   and to_char(e.end_date,'YYYY-MM-DD')<='${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e1.build_Area >= " + buildAreaStart
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e1.build_Area <= " + buildAreaEnd
        }
        if (dynamicMortgageRateStart && dynamicMortgageRateEnd)
        {
            def start = Integer.parseInt(dynamicMortgageRateStart.toString()) / 100
            def end = Integer.parseInt(dynamicMortgageRateEnd.toString()) / 100
            sql += " and e.cltv > ${start} AND e.cltv <= ${end}  "
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and e.cltv > ${minValue} and  e.cltv <= ${maxValue} "
                }
            }
        }
        sql += " group by d.label "
        sql += " union select d1.order_number as orderNumber,d1.label,0,0,0,0 from dimension d1 " + "  where d1.class='com.cindata.dimension.YearLimitDimension' " + "  group by d1.label,d1.order_number)  tt " + "  group by tt.subsection"

        def result = db.rows(sql).asList()
        result.each {
            if (it.loansEvaluateTotalPrice == 0 || it.loansBalance == 0)
            {
                it.ltv = 0
            }
            else
            {
                def cltv = ((it.loansBalance / it.loansEvaluateTotalPrice) * 100).setScale(2, BigDecimal.ROUND_HALF_UP)
                it.ltv = cltv
            }

        }
        render([result: result] as JSON)
    }

    def common()
    {
        respond(view: "common")
    }

    /************************************还款情况分析-张成远********************************************/
    /**
     * 每月还清贷款数量
     * @return
     */
    @Transactional
    def repaymentAmountPerMonth()
    {
        respond(view: "repaymentAmountPerMonth")
    }

    def repaymentAmountPerMonth1()
    {
        respond(view: "repaymentAmountPerMonth1")
    }

    /**
     *  每月还清贷款数量(机构过滤)
     * @return
     */
    def repaymentAmountPerMonthData()
    {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM")
        Calendar c = Calendar.getInstance()
        def monthList = []
        for (
            i in
                0..11)
        {
            c = Calendar.getInstance()
            c.add(Calendar.MONTH, i - 11);
            monthList.add(format.format(c.getTime()))
        }

        def sql = "select new map(count(m.mortgage.id) as esateCount, to_char(m.mortgage.payOffAt,'yyyy-mm') as payOffAt) from MortgageCollateral m where m.mortgage.loansBalance = 0"
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql}"
        }
        sql += pubConditionSql(params) + " group by to_char(m.mortgage.payOffAt,'yyyy-mm') order by payOffAt "
        def result = MortgageCollateral.executeQuery(sql).asList()

        def resultList = []
        for (
            def i = 0;
                i < monthList?.size();
                i++)
        {
            def resultMap = [esateCount: 0, payOffAt: monthList[i]]
            for (
                def j = 0;
                    j < result?.size();
                    j++)
            {
                if (monthList[i] == result[j]?.payOffAt)
                {
                    resultMap.esateCount = result[j].esateCount
                }
            }
            resultList.add(resultMap)
        }

        println "sql:" + sql
        println "resultList:" + resultList

        render([result: resultList] as JSON)
    }

    /**
     * 每年还清贷款结构分布
     * @return
     */
    @Transactional
    def repaymentStructPerYear()
    {
        respond(view: "repaymentStructPerYear")
    }

    def repaymentStructPerYear1()
    {
        respond(view: "repaymentStructPerYear1")
    }

    /**
     * 每年还清贷款结构分布(机构过滤)
     * @return
     */
    def repaymentStructPerYearData()
    {
        SimpleDateFormat format = new SimpleDateFormat("yyyy")
        Calendar c = Calendar.getInstance()
        def yearsList = []
        for (
            i in
                0..9)
        {
            c = Calendar.getInstance()
            c.add(Calendar.YEAR, i - 9);
            yearsList.add(format.format(c.getTime()))
        }

        def sql = "select new map(count(case when m.mortgage.loansBalance = 0 then 1 else null end) as esateCount1, count(case when m.mortgage.loansBalance <> 0 then 1 else null end) as esateCount2, to_char(m.mortgage.payOffAt,'yyyy') as payOffAt) from MortgageCollateral m where 1=1"
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql}"
        }
        sql += pubConditionSql(params) + " group by to_char(m.mortgage.payOffAt,'yyyy') order by payOffAt "

        def result = Mortgage.executeQuery(sql).asList()

        def resultList = []
        for (
            def i = 0;
                i < yearsList?.size();
                i++)
        {
            def resultMap = [esateCount1: 0, esateCount2: 0, payOffAt: yearsList[i]]
            for (
                def j = 0;
                    j < result?.size();
                    j++)
            {
                if (yearsList[i] == result[j]?.payOffAt)
                {
                    resultMap.esateCount1 = result[j].esateCount1
                    resultMap.esateCount2 = result[j].esateCount2
                }
            }
            resultList.add(resultMap)
        }

        println "sql:" + sql
        println "resultList:" + resultList

        render([result: resultList] as JSON)
    }

    /**
     * 贷款还清时间统计
     * @return
     */
    @Transactional
    def repaymentDateStatistics()
    {
        respond(view: "repaymentDateStatistics")
    }

    def repaymentDateStatistics1()
    {
        respond(view: "repaymentDateStatistics1")
    }

    /**
     * 贷款还清时间统计
     * @return
     */
    // def repaymentDateStatisticsData()
    // {
    //     def sql = "select to_char(m.pay_off_at, 'yyyy') as payoffat,"
    //     sql += " count(case when m.end_date - m.start_date <= interval '1 year' then 1 else null end) as inoneyear,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '1 year' and m.end_date - m.start_date <= interval '2 year' then 1 else null end) as intowyears,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '2 year' and m.end_date - m.start_date <= interval '3 year' then 1 else null end) as inthreeyears,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '3 year' and m.end_date - m.start_date <= interval '4 year' then 1 else null end) as infouryears,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '4 year' and m.end_date - m.start_date <= interval '5 year' then 1 else null end) as infiveyears,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '5 year' and m.end_date - m.start_date <= interval '10 year' then 1 else null end) as intenyears,"
    //     sql += " count(case when m.end_date - m.start_date >  interval '10 year' then 1 else null end) as aboveTenYears"
    //     sql += " from mortgage as m left join estate as e on m.estate_id = e.id left join area as a on e.city_id = a.area_id left join area as b on e.district_id = b.area_id where m.loans_balance = 0 "
    //     sql += pubConditionNativeSql(params)
    //     sql += " group by to_char(m.pay_off_at,'yyyy') order by payoffat"

    //     def db = new groovy.sql.Sql(dataSource)
    //     def result = db.rows(sql).asList()

    //     db.close()

    //     println "sql:" + sql
    //     println "result:" + result

    //     render([result: result] as JSON)
    // }

    /**
     * 贷款还清时间统计（机构过滤）
     * @return
     */
    def repaymentDateStatisticsData()
    {
        def sql = "select new map(to_char(m.mortgage.payOffAt, 'yyyy') as payoffat,"
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 0 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 12) then 1 else null end) as inoneyear, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 12 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 24) then 1 else null end) as intowyears, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 24 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 36) then 1 else null end) as inthreeyears, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 36 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 48) then 1 else null end) as infouryears, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 48 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 60) then 1 else null end) as infiveyears, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 60 and months_between(m.mortgage.endDate, m.mortgage.startDate) <= 120) then 1 else null end) as intenyears, "
        sql += "count(case when (months_between(m.mortgage.endDate, m.mortgage.startDate) > 120) then 1 else null end) as abovetenyears) "
        sql += "from MortgageCollateral m where m.mortgage.loansBalance = 0"
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql}"
        }
        sql += pubConditionSql(params) + " group by to_char(m.mortgage.payOffAt,'yyyy') order by payoffat"

        def result = Mortgage.executeQuery(sql).asList()

        println "sql:" + sql
        println "result:" + result

        render([result: result] as JSON)
    }

    /************************************公积金贷款分析-张成远********************************************/
    /**
     * 贷款发放基本情况
     * @return
     */
    @Transactional
    def loanGrantingBasicSituation()
    {
        respond(view: "loanGrantingBasicSituation")
    }

    /**
     * 贷款发放基本情况（机构过滤）
     * @return
     */
    def loanGrantingBasicSituationData()
    {
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        def sql = "select new map(to_char(m.mortgage.startDate, 'yyyy') as startDate,"
        sql += "count(case when m.mortgage.estateType.code = 'FIRSTFORWARD' then 1 else null end) as FIRSTFORWARD, "
        sql += "count(case when m.mortgage.estateType.code = 'FIRST' then 1 else null end) as FIRST, "
        sql += "count(case when m.mortgage.estateType.code = 'SECOND' then 1 else null end) as SECOND) "
        sql += "from MortgageCollateral m where m.collateral.estateStatus.code = '1'"
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql}"
        }
        sql += pubConditionSql(params)
        sql += " group by to_char(m.mortgage.startDate,'yyyy') order by startDate "

        def sql1 = "select new map(to_char(m.mortgage.startDate, 'yyyy') as startDate,"
        sql1 += "count(case when m.mortgage.isFirstHouse = true then 1 else null end) as ISFIRSTHOUSE, "
        sql1 += "count(case when m.mortgage.isFirstHouse = false then 1 else null end) as ISNOTFIRSTHOUSE) "
        sql1 += "from MortgageCollateral m where m.collateral.estateStatus.code = '1'"
        if (accountSql)
        {
            sql1 += " and m.collateral.account.id in ${accountSql}"
        }
        sql1 += pubConditionSql(params)
        sql1 += " group by to_char(m.mortgage.startDate,'yyyy') order by startDate"

        def result = Mortgage.executeQuery(sql).asList()
        def result1 = Mortgage.executeQuery(sql1).asList()

        println "sql:" + sql
        println "result:" + result
        println "sql1:" + sql1
        println "result1:" + result1

        render([result: result, result1: result1] as JSON)
    }

    /**
     * 每月贷款发放数量
     * @return
     */
    @Transactional
    def loanAmountPerMonth()
    {
        respond(view: "loanAmountPerMonth")
    }

    /**
     * 每月贷款发放数量(机构过滤)
     * @return
     */
    def loanAmountPerMonthData()
    {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM")
        Calendar c = Calendar.getInstance()
        def monthList = []
        for (
            i in
                0..11)
        {
            c = Calendar.getInstance()
            c.add(Calendar.MONTH, i - 11);
            monthList.add(format.format(c.getTime()))
        }

        def sql = "select new map(to_char(m.mortgage.startDate, 'yyyy-MM') as startDate, count(case when m.mortgage.estateType.code = 'FIRSTFORWARD' then 1 else null end) as FIRSTFORWARD, count(case when m.mortgage.estateType.code = 'FIRST' then 1 else null end) as FIRST, count(case when m.mortgage.estateType.code = 'SECOND' then 1 else null end) as SECOND) from MortgageCollateral m where 1=1"
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql}"
        }
        sql += pubConditionSql(params) + " group by to_char(m.mortgage.startDate,'yyyy-MM') order by startDate"

        def result = Mortgage.executeQuery(sql).asList()

        def resultList = []
        for (
            def i = 0;
                i < monthList?.size();
                i++)
        {
            def resultMap = [SECOND: 0, FIRST: 0, FIRSTFORWARD: 0, startDate: monthList[i]]
            for (
                def j = 0;
                    j < result?.size();
                    j++)
            {
                if (monthList[i] == result[j]?.startDate)
                {
                    resultMap.SECOND = result[j].SECOND
                    resultMap.FIRST = result[j].FIRST
                    resultMap.FIRSTFORWARD = result[j].FIRSTFORWARD
                }
            }
            resultList.add(resultMap)
        }

        println "sql:" + sql
        println "resultList:" + resultList

        render([result: resultList] as JSON)
    }

    /**
     * 贷款发放结构分布（机构过滤）
     * @return
     */
    @Transactional
    def loanGrantingStructure()
    {
        respond(view: "loanGrantingStructure")
    }

    /**
     *  贷款发放结构分布
     * @return
     */
    def loanGrantingStructureData()
    {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM")
        Calendar c = Calendar.getInstance()
        def monthList = []
        for (
            i in
                0..11)
        {
            c = Calendar.getInstance()
            c.add(Calendar.MONTH, i - 11);
            monthList.add(format.format(c.getTime()))
        }

        def sql = "select new map(to_char(m.mortgage.startDate,'yyyy-mm') as startDate, count(case when m.mortgage.isFirstHouse = true then 1 else null end) as firstHouseCount, case when count(case when m.mortgage.isFirstHouse = true then 1 else null end) <> 0 then (sum(case when m.mortgage.isFirstHouse = true then m.mortgage.unitPrice else 0 end)/count(case when m.mortgage.isFirstHouse = true then 1 else null end)) else 0 end as firstHouseUnitPrice, count(case when m.mortgage.isFirstHouse = false then 1 else null end) as notFirstHouseCount, case when count(case when m.mortgage.isFirstHouse = false then 1 else null end) <> 0 then (sum(case when m.mortgage.isFirstHouse = false then m.mortgage.unitPrice else 0 end)/count(case when m.mortgage.isFirstHouse = false then 1 else null end)) else 0 end as notFirstHouseUnitPrice) from MortgageCollateral m where 1=1 "
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and m.collateral.account.id in ${accountSql} "
        }
        sql += pubConditionSql(params)
        sql += " group by to_char(m.mortgage.startDate,'yyyy-mm') order by startDate "
        def result = Mortgage.executeQuery(sql).asList()

        def resultList = []
        for (
            def i = 0;
                i < monthList?.size();
                i++)
        {
            def resultMap = [notFirstHouseCount: 0, notFirstHouseUnitPrice: 0, firstHouseUnitPrice: 0, firstHouseCount: 0, startDate: monthList[i]]
            for (
                def j = 0;
                    j < result?.size();
                    j++)
            {
                if (monthList[i] == result[j]?.startDate)
                {
                    resultMap.notFirstHouseCount = result[j].notFirstHouseCount
                    resultMap.notFirstHouseUnitPrice = result[j].notFirstHouseUnitPrice
                    resultMap.firstHouseUnitPrice = result[j].firstHouseUnitPrice
                    resultMap.firstHouseCount = result[j].firstHouseCount
                }
            }
            resultList.add(resultMap)
        }

        println "sql:" + sql
        println "resultList:" + resultList

        render([result: resultList] as JSON)
    }

    /************************************押品分析-王萱********************************************/
    /**
     * 季度时间轴数据*/
    @Transactional
    def timesLineForQuarter(int yearLength)
    {
        //1:一年 2:两年
        Calendar a = Calendar.getInstance()
        //当前年
        int endYear = a.get(Calendar.YEAR)
        //当前月
        int endMonth = a.get(Calendar.MONTH) + 1
        //开始日期
        a.add(Calendar.MONTH, -(yearLength * 12 - 1))
        int startYear = a.get(Calendar.YEAR)
        int startMonth = a.get(Calendar.MONTH) + 1
        def quarterList = new ArrayList()
        try
        {
            def list = getQuarterList(startYear, startMonth, endYear, endMonth)
            //去掉重复
            for (
                int i = 0;
                    i < list.size();
                    i++)
            {
                String str = list.get(i)
                if (!quarterList.contains(str))
                {
                    quarterList.add(str)
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace()
        }
        return quarterList
    }

    def getQuarterList(int startYear, int startMonth, int endYear, int endMonth)
    {
        def quarterList = new ArrayList()
        if (endYear - startYear == 0)
        {
            for (
                int i = startMonth;
                    i <= endMonth;
                    i++)
            {
                quarterList.add(getQuarterStr(startYear, i))
            }
        }
        else if (endYear - startYear == 1)
        {
            for (
                int i = startMonth;
                    i <= 12;
                    i++)
            {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                int i = 1;
                    i <= endMonth;
                    i++)
            {
                quarterList.add(getQuarterStr(endYear, i))
            }
        }
        else if (endYear - startYear == 2)
        {
            for (
                int i = startMonth;
                    i <= 12;
                    i++)
            {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                int i = 1;
                    i <= 12;
                    i++)
            {
                quarterList.add(getQuarterStr(startYear + 1, i))
            }
            for (
                int i = 1;
                    i <= endMonth;
                    i++)
            {
                quarterList.add(getQuarterStr(endYear, i))
            }
        }
        else
        {
            for (
                int i = startMonth;
                    i <= 12;
                    i++)
            {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                int i = startYear + 1;
                    i <= endYear - 1;
                    i++)
            {
                for (
                    int j = 1;
                        j <= 12;
                        j++)
                {
                    quarterList.add(getQuarterStr(i, j))
                }
            }
            for (
                int i = 1;
                    i <= endMonth;
                    i++)
            {
                quarterList.add(getQuarterStr(endYear, i))
            }
        }
        return quarterList
    }

    def getQuarterStr(int year, int month)
    {
        def quarterStr = ""
        switch (month)
        {
        case 1:
        case 2:
        case 3:
            quarterStr += year + "一季度"
            break
        case 4:
        case 5:
        case 6:
            quarterStr += year + "二季度"
            break
        case 7:
        case 8:
        case 9:
            quarterStr += year + "三季度"
            break
        case 10:
        case 11:
        case 12:
            quarterStr += year + "四季度"
            break
        }
        return quarterStr
    }

    /**
     * 公用条件查询*/
    def pubSql(params)
    {
        //城市
        def city = params['city']
        //行政区
        def district = params['district']
        //贷款余额上限
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款金额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //放款时间开始
        def startDateStart = params["startDateStart"]
        //放款时间结束
        def startDateEnd = params["startDateEnd"]
        //面积上限
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //贷款到期日开始
        def endDateStart = params["endDateStart"]
        //贷款到期日结束
        def endDateEnd = params["endDateEnd"]
        //贷款时长
        def loansDuration = params["loansDuration"]
        //风险
        def risk = params["risk"]
        def sql = ""
        //查询条件
        if (city && city != '-1')
        {
            sql += " AND A.ZONE_NUMBER = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " AND B.ZONE_NUMBER = '${district}'"
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " AND E.LOANS_BALANCE >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " AND E.LOANS_BALANCE <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " AND E.LOANS_AMOUNT >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " AND E.LOANS_AMOUNT <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart && startDateStart != '')
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (startDateEnd && startDateEnd != '')
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " AND E1.SOURCE_BUILD_AREA >= '${buildAreaStart}'"
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " AND E1.SOURCE_BUILD_AREA <= '${buildAreaEnd}'"
        }
        if (endDateStart && endDateStart != '')
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (endDateEnd && endDateEnd != '')
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def minValue = it.minValue
                    def maxValue = it.maxValue
                    sql += " AND E.LOANS_DURATION > '${minValue}' AND E.LOANS_DURATION <= '${maxValue}'"
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " AND E.CLTV > '${minValue}' AND E.CLTV <= '${maxValue}'"
                }

            }
        }
        return sql
    }

    /**
     * 公用条件查询*/
    def pubSqlNoCityAndDistrict(params)
    {
        //贷款余额上限
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款金额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //放款时间开始
        def startDateStart = params["startDateStart"]
        //放款时间结束
        def startDateEnd = params["startDateEnd"]
        //面积上限
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //贷款到期日开始
        def endDateStart = params["endDateStart"]
        //贷款到期日结束
        def endDateEnd = params["endDateEnd"]
        //贷款时长
        def loansDuration = params["loansDuration"]
        //风险
        def risk = params["risk"]
        def sql = ""
        //查询条件
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " AND E.LOANS_BALANCE >= ${loansBalanceStart}*10000"
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " AND E.LOANS_BALANCE <= ${loansBalanceEnd}*10000"
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " AND E.LOANS_AMOUNT >= ${loansAmountStart}*10000"
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " AND E.LOANS_AMOUNT <= ${loansAmountEnd}*10000"
        }
        if (startDateStart && startDateStart != '')
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (startDateEnd && startDateEnd != '')
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " AND E1.SOURCE_BUILD_AREA >= '${buildAreaStart}'"
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " AND E1.SOURCE_BUILD_AREA <= '${buildAreaEnd}'"
        }
        if (endDateStart && endDateStart != '')
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (endDateEnd && endDateEnd != '')
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " AND TO_CHAR(E.END_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def minValue = it.minValue
                    def maxValue = it.maxValue
                    sql += " AND E.LOANS_DURATION > '${minValue}' AND E.LOANS_DURATION <= '${maxValue}'"
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " AND E.CLTV > '${minValue}' AND E.CLTV <= '${maxValue}'"
                }

            }
        }
        return sql
    }

    /**
     * 押品性质分析*/
    @Transactional
    def mortgageProperty()
    {
        respond(view: "mortgageProperty")
    }

    /**
     * 押品性质分析 （机构过滤）*/
    @Transactional
    def mortgagePropertyData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def yearLength = params['yearLength'] ? Integer.parseInt(params['yearLength']) : 2
        def result = new ArrayList()
        def resultList = new ArrayList()
        def status = ""
        try
        {
            def quarterList = timesLineForQuarter(yearLength)
            for (
                String yearQuarter :
                    quarterList)
            {
                def year = yearQuarter.substring(0, 4)
                def quarter = yearQuarter.substring(4)
                def sql = " SELECT TT.NAME AS NAME , SUM(TT.COUNT) AS COUNT FROM ( SELECT D.NAME AS NAME ,CASE WHEN COUNT(E.ID) IS NULL THEN 0 ELSE COUNT(E.ID) END AS COUNT FROM MORTGAGE E INNER JOIN DATA_DICT D ON E.ESTATE_TYPE_ID = D.ID AND D.CLASS = 'com.cindata.dictionary.HouseTypeDict' LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1"
                def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
                if (accountSql)
                {
                    sql += " and E1.account_id in ${accountSql} "
                }
                sql += " and E1.ESTATE_STATUS_ID = ( SELECT DT.ID FROM DATA_DICT DT WHERE DT.CODE = '1' AND DT.CLASS = 'com.cindata.dictionary.EstateStatusDict') "
                sql += pubSql(params)
                if (quarter == '一季度')
                {
                    def startTime = year + "-01-01 00:00:00"
                    def endTime = year + "-03-31 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '二季度')
                {
                    def startTime = year + "-04-01 00:00:00"
                    def endTime = year + "-06-30 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '三季度')
                {
                    def startTime = year + "-07-01 00:00:00"
                    def endTime = year + "-09-30 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '四季度')
                {
                    def startTime = year + "-10-01 00:00:00"
                    def endTime = year + "-12-31 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                sql += " GROUP BY D.NAME "
                sql += " UNION SELECT D1.NAME AS NAME ,0 AS COUNT FROM DATA_DICT D1 WHERE D1.CLASS ='com.cindata.dictionary.HouseTypeDict'  GROUP BY D1.NAME ) TT GROUP BY TT.NAME "

                result = db.rows(sql).asList()
                def map = new HashMap()
                map.put("YEARQUARTER", yearQuarter)
                map.put("COUNTLIST", result)
                resultList.add(map)
            }
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([resultList: resultList, status: status] as JSON)
    }

    /**
     * 押品历史平均单价*/
    @Transactional
    def historyAverageUnitPrice()
    {
        respond(view: "historyAverageUnitPrice")
    }

    /**
     * 押品历史平均单价数据（机构过滤）*/
    @Transactional
    def historyAverageUnitPriceData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def offset = params.max ? params.max.toInteger() : 0
        def yearLength = params['yearLength'] ? Integer.parseInt(params['yearLength']) : 2
        def result = new ArrayList()
        def resultList = new ArrayList()
        def status = ""
        try
        {
            def quarterList = timesLineForQuarter(yearLength)
            for (
                String yearQuarter :
                    quarterList)
            {
                def citySql = " "
                def sql = " SELECT CC.ZONENUMBER AS ZONENUMBER ,CC.NAME AS NAME ,SUM(CC.PRICE) AS PRICE FROM ( "
                if (params['city'] && params['city'] != '-1')
                {
                    citySql += " B.ZONE_NUMBER AS ZONENUMBER,B.NAME AS NAME , "
                }
                else
                {
                    citySql += " A.ZONE_NUMBER AS ZONENUMBER,A.NAME AS NAME , "
                }
                if (params['city'] && params['city'] != '-1')
                {
                    sql += " SELECT * FROM ( SELECT " + citySql + " CASE WHEN AVG(E.LOANS_EVALUATE_PRICE) IS NULL THEN 0 ELSE AVG(E.LOANS_EVALUATE_PRICE) END AS PRICE  FROM MORTGAGE E  LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1 "
                }
                else
                {
                    sql += " SELECT * FROM ( SELECT " + citySql + " CASE WHEN AVG(E.LOANS_EVALUATE_PRICE) IS NULL THEN 0 ELSE AVG(E.LOANS_EVALUATE_PRICE) END AS PRICE  FROM MORTGAGE E  LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1 "
                }
                def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
                if (accountSql)
                {
                    sql += " and E1.ORG_ID in ${accountSql} "
                }
                sql += pubSql(params)
                def year = yearQuarter.substring(0, 4)
                def quarter = yearQuarter.substring(4)
                if (quarter == '一季度')
                {
                    def startTime = year + "-01-01 00:00:00"
                    def endTime = year + "-03-31 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '二季度')
                {
                    def startTime = year + "-04-01 00:00:00"
                    def endTime = year + "-06-30 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '三季度')
                {
                    def startTime = year + "-07-01 00:00:00"
                    def endTime = year + "-09-30 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                else if (quarter == '四季度')
                {
                    def startTime = year + "-10-01 00:00:00"
                    def endTime = year + "-12-31 23:59:59"
                    sql += " AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') >= '${startTime}' AND TO_CHAR(E.START_DATE,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }
                if (params['city'] && params['city'] != '-1')
                {
                    sql += " GROUP BY B.ZONE_NUMBER,B.NAME ORDER BY PRICE DESC ) AA "
                    sql += " UNION SELECT * FROM ( SELECT D.ZONE_NUMBER AS ZONENUMBER ,D.NAME AS NAME ,0 AS PRICE FROM  AREA D WHERE D.AREA_ID IN ( SELECT DISTINCT EE.DISTRICT_ID FROM ESTATE EE WHERE EE.CITY_ID = ( SELECT AA.AREA_ID FROM AREA AA WHERE AA.ZONE_NUMBER = '${params['city']}')) GROUP BY D.ZONE_NUMBER ,D.NAME ) DD ) CC  GROUP BY CC.ZONENUMBER , CC.NAME ORDER BY PRICE DESC "
                    result = db.rows(sql).asList()
                }
                else
                {
                    //sql += " GROUP BY A.ZONE_NUMBER,A.NAME ORDER BY PRICE DESC ) EE WHERE ROWNUM < ${max} ) BB "
                    //pg数据库
                    sql += " GROUP BY A.ZONE_NUMBER,A.NAME ORDER BY PRICE DESC ) BB "
                    sql += " UNION SELECT * FROM ( SELECT D.ZONE_NUMBER AS ZONENUMBER ,D.NAME AS NAME ,0 AS PRICE FROM  AREA D WHERE D.AREA_ID IN ( SELECT DISTINCT EE.CITY_ID FROM ESTATE EE ) GROUP BY D.ZONE_NUMBER ,D.NAME ) DD ) CC  GROUP BY CC.ZONENUMBER , CC.NAME ORDER BY PRICE DESC "
                    result = db.rows(sql, offset, max).asList()
                }
                def map = new HashMap()
                map.put("YEARQUARTER", yearQuarter)
                map.put("PRICELIST", result)
                resultList.add(map)
            }
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([resultList: resultList, status: status] as JSON)
    }

    /************************************借款人分析-王萱********************************************/
    /**
     * 借款人户口性质分布*/
    @Transactional
    def mortgagorHouseholdRegister()
    {
        respond(view: "mortgagorHouseholdRegister")
    }

    def mortgagorHouseholdRegister1()
    {
        respond(view: "mortgagorHouseholdRegister1")
    }

    /**
     * 借款人户口性质分布数据 （机构过滤） */
    @Transactional
    def mortgagorHouseholdRegisterData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        def status = ""
        try
        {
            def sql = " SELECT TT.NAME AS NAME , SUM(TT.COUNT) AS COUNT FROM ( SELECT D.NAME AS NAME ,CASE WHEN COUNT(E.ID) IS NULL THEN 0 ELSE COUNT(E.ID) END AS COUNT FROM MORTGAGE E INNER JOIN DATA_DICT D ON E.BORR_ACCOUNT_NATURE_ID = D.ID AND D.CLASS = 'com.cindata.dictionary.AccountNatureDict' LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1 "
            def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            if (accountSql)
            {
                sql += " and E1.ORG_ID in ${accountSql} "
            }
            sql += " and E1.ESTATE_STATUS_ID = ( SELECT DT.ID FROM DATA_DICT DT WHERE DT.CODE = '1' AND DT.CLASS = 'com.cindata.dictionary.EstateStatusDict') "
            sql += pubSql(params)
            sql += " GROUP BY D.NAME "
            sql += " UNION SELECT D1.NAME AS NAME ,0 AS COUNT FROM DATA_DICT D1 WHERE D1.CLASS ='com.cindata.dictionary.AccountNatureDict'  GROUP BY D1.NAME ) TT GROUP BY TT.NAME "
            result = db.rows(sql).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }

    /**
     * 借款人缴存基数（机构过滤）*/
    @Transactional
    def mortgagorDepositBase()
    {
        respond(view: "mortgagorDepositBase")
    }

    def mortgagorDepositBase1()
    {
        respond(view: "mortgagorDepositBase1")
    }

    /**
     * 借款人缴存基数数据*/
    @Transactional
    def mortgagorDepositBaseData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        def status = ""
        try
        {
            def sql = " SELECT SUM(TT.ORDERNUMBER) AS ORDERNUMBE,TT.SUBSECTION AS SUBSECTION , SUM(TT.COUNT) AS COUNT FROM ( SELECT 0 AS ORDERNUMBER ,D.LABEL AS SUBSECTION ,CASE WHEN COUNT(E.ID) IS NULL THEN 0 ELSE COUNT(E.ID) END AS COUNT FROM MORTGAGE E INNER JOIN DIMENSION D ON  E.BORR_DEPOSIT_BASE_ON_LOANS > D.MIN_VALUE and E.BORR_DEPOSIT_BASE_ON_LOANS <= D.MAX_VALUE AND D.CLASS = 'com.cindata.dimension.DepositBaseDimension' LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1 "
            def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            if (accountSql)
            {
                sql += " and E1.ORG_ID in ${accountSql} "
            }
            sql += " and E1.ESTATE_STATUS_ID = ( SELECT DT.ID FROM DATA_DICT DT WHERE DT.CODE = '1' AND DT.CLASS = 'com.cindata.dictionary.EstateStatusDict') "
            sql += pubSql(params)
            sql += " GROUP BY D.LABEL "
            sql += " UNION SELECT D1.ORDER_NUMBER AS ORDERNUMBER ,D1.LABEL AS SUBSECTION ,0 AS COUNT FROM DIMENSION D1 WHERE D1.CLASS ='com.cindata.dimension.DepositBaseDimension'  GROUP BY D1.ORDER_NUMBER ,D1.LABEL) TT GROUP BY TT.SUBSECTION "
            result = db.rows(sql).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }

    /**
     * 借款人年龄分析*/
    @Transactional
    def mortgagorAge()
    {
        respond(view: "mortgagorAge")
    }

    def mortgagorAge1()
    {
        respond(view: "mortgagorAge1")
    }

    /**
     * 借款人年龄分析数据 （机构过滤）*/
    @Transactional
    def mortgagorAgeData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def result = new ArrayList()
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat('yyyy-MM-dd')
        def currentDate = simpleDateFormat.format(new Date())
        def status = ""
        try
        {
            def sql = " SELECT SUM(TT.ORDERNUMBER) AS ORDERNUMBE,TT.SUBSECTION AS SUBSECTION , SUM(TT.COUNT) AS COUNT FROM ( SELECT 0 AS ORDERNUMBER ,D.LABEL AS SUBSECTION ,CASE WHEN COUNT(E.ID) IS NULL THEN 0 ELSE COUNT(E.ID) END AS COUNT FROM MORTGAGE E INNER JOIN DIMENSION D ON  MONTHS_BETWEEN(TO_DATE('${currentDate}','YYYY-MM-DD'),E.BORR_BIRTHDAY) > D.MIN_VALUE and MONTHS_BETWEEN(TO_DATE('${currentDate}','YYYY-MM-DD'),E.BORR_BIRTHDAY) <= D.MAX_VALUE AND D.CLASS = 'com.cindata.dimension.AgeDimension' LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE 1=1 "
            def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            if (accountSql)
            {
                sql += " and E1.ORG_ID in " + accountSql
            }
            sql += " and E1.ESTATE_STATUS_ID = ( SELECT DT.ID FROM DATA_DICT DT WHERE DT.CODE = '1' AND DT.CLASS = 'com.cindata.dictionary.EstateStatusDict') "
            //pg数据库
            //def sql = " SELECT SUM(TT.ORDERNUMBER) AS ORDERNUMBE,TT.SUBSECTION AS SUBSECTION , SUM(TT.COUNT) AS COUNT FROM ( SELECT 0 AS ORDERNUMBER ,D.LABEL AS SUBSECTION ,CASE WHEN COUNT(E.ID) IS NULL THEN 0 ELSE COUNT(E.ID) END AS COUNT FROM MORTGAGE E INNER JOIN DIMENSION D ON  (SELECT ( EXTRACT(YEAR FROM TO_DATE('${currentDate}','YYYY-MM-DD')) -EXTRACT(YEAR FROM E.BORR_BIRTHDAY) ) * 12 +( EXTRACT(MONTH FROM TO_DATE('${currentDate}','YYYY-MM-DD')) -EXTRACT(MONTH FROM E.BORR_BIRTHDAY))) > D.MIN_VALUE and (SELECT ( EXTRACT(YEAR FROM TO_DATE('${currentDate}','YYYY-MM-DD')) -EXTRACT(YEAR FROM E.BORR_BIRTHDAY) ) * 12 +( EXTRACT(MONTH FROM TO_DATE('${currentDate}','YYYY-MM-DD')) -EXTRACT(MONTH FROM E.BORR_BIRTHDAY))) <= D.MAX_VALUE AND D.CLASS = 'com.cindata.dimension.AgeDimension' LEFT JOIN ESTATE E1 ON E.ESTATE_ID = E1.ID LEFT JOIN AREA A ON E1.CITY_ID = A.AREA_ID LEFT JOIN AREA B ON E1.DISTRICT_ID = B.AREA_ID WHERE E1.ESTATE_STATUS_ID = ( SELECT DT.ID FROM DATA_DICT DT WHERE DT.CODE = '1' ) "
            sql += pubSql(params)
            sql += " GROUP BY D.LABEL "
            sql += " UNION SELECT D1.ORDER_NUMBER AS ORDERNUMBER ,D1.LABEL AS SUBSECTION ,0 AS COUNT FROM DIMENSION D1 WHERE D1.CLASS ='com.cindata.dimension.AgeDimension'  GROUP BY D1.ORDER_NUMBER ,D1.LABEL) TT GROUP BY TT.SUBSECTION "
            println sql
            result = db.rows(sql).asList()
            status = "success"
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([result: result, status: status] as JSON)
    }

    /************************************逾期行为分析-于鹏杰********************************************/
    /**
     * 逾期行为总体概况*/
    @Transactional
    def overdueActionCollectivityAnalysis()
    {
        respond(view: "overdueActionCollectivityAnalysis")
    }

    def overdueActionCollectivityAnalysis1()
    {
        respond(view: "overdueActionCollectivityAnalysis1")
    }

    /**
     * 逾期行为总体概况数据 （机构过滤）*/
    @Transactional
    def overdueActionCollectivityAnalysisData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result = new ArrayList()
        def resultList = new ArrayList()
        def status = ""
        def pubsql = pubSql(params)
        try
        {
            def sql = "select " + "count(case when T1.QINUM= 1 then 1 else null end)  as qicount1," + "count(case when T1.QINUM>1 and T1.QINUM<=6  then 1 else null end)  as qicount2," + "count(case when T1.QINUM>6  then 1 else null end)  as qicount3," + "sum(case when T1.QINUM= 1 then T1.AMOUNT else 0 end)  as amount1," + "sum(case when T1.QINUM>1 and T1.QINUM<=6  then T1.AMOUNT else 0 end)  as amount2," + "sum(case when T1.QINUM>6  then T1.AMOUNT else 0 end)  as amount3" + " from (select RE.id, re.OVERDUE_STATE_ID as state , re.CONTINUOUS_OVERDUE_NUMBER as qinum ," + " re.should_repayment_amount+re.fine_amount as amount " + " from repayment_import re " + " left join mortgage e  on  re.mortgage_id = e.id " + "  left join estate e1 on e.estate_id = e1.id  left join area a on a.area_id = e1.city_id " + " left join area b on a.area_id = e1.district_id where 1=1"
            def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            if (accountSql)
            {
                sql += " and e1.account_id in ${accountSql} "
            }
            sql += " and RE.CONTINUOUS_OVERDUE_NUMBER > 0 " + " " + pubsql + " and RE.OVERDUE_STATE_ID = (select id from data_dict  where class = 'com.cindata.dictionary.OverdueStateDict' and code = 'NoPay')) t1";
            println sql;
            result = db.rows(sql).asList();
            resultList.add(["counts": result[0].qicount1, "qinum": '逾期1期', "amount": result[0].amount1 == null ? 0 : result[0].amount1])
            resultList.add(["counts": result[0].qicount2, "qinum": '逾期2-6期', "amount": result[0].amount2 == null ? 0 : result[0].amount2])
            resultList.add(["counts": result[0].qicount3, "qinum": '逾期6期以上', "amount": result[0].amount3 == null ? 0 : result[0].amount3])
            status = "success"
            println resultList
        }
        catch (Exception e)
        {
            e.printStackTrace()
            status = "false"
        }
        render([resultList: resultList, status: status] as JSON)
    }

    /**
     * 逾期行为结构分析*/
    @Transactional
    def overdueActionStructureAnalysis()
    {
        respond(view: "overdueActionStructureAnalysis")
    }

    def overdueActionStructureAnalysis1()
    {
        respond(view: "overdueActionStructureAnalysis1")
    }

    /**
     * 逾期行为结构分析数据（机构过滤）*/
    @Transactional
    def overdueActionStructureAnalysisData()
    {
        def db = new groovy.sql.Sql(dataSource)
        def max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result = new ArrayList()
        def resultList = new ArrayList()
        def status = ""
        def key = params['key']
        def city = params['city']
        def district = params['district']
        def sql = ""
        def pubsql = pubSql(params)
        def pubsqlNoCity = pubSqlNoCityAndDistrict(params);
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            pubsql += " and e1.account_id in ${accountSql} "
            pubsqlNoCity += " and e1.account_id in ${accountSql} "
        }

        //区域统计
        if (key == "1" || key == null)
        {
            if (city != null && city != "-1")
            {
                //查询出该市的id
                def area = Area.findByZoneNumber(city);
                //城市不为空的情况下，按地区分组
                sql = "select count(T1.ID) as counts,sum(T1.amount) as amounts,T1.disname  from (" + "SELECT r.ID,r.SHOULD_REPAYMENT_AMOUNT+r.FINE_AMOUNT as amount,a.name as disname from REPAYMENT_IMPORT r " + " LEFT JOIN mortgage e on r.mortgage_ID = e.ID left join estate e1 on e.estate_id = e1.id " + " LEFT JOIN AREA a on a.area_id = e1.district_id" + " left join area b on b.area_id = e1.city_id" + " where a.zone_number in (select zone_number from area where parent_area =  " + area.id + ")" + pubsqlNoCity + " and e1.estate_status_id = (select id from data_dict where code = '1' and class = 'com.cindata.dictionary.EstateStatusDict') ) t1 group  by t1.disname"
            }
            else
            {
                sql = "select count(T1.ID) as counts,sum(T1.amount) as amounts,T1.disname  from (" + "SELECT r.ID,r.SHOULD_REPAYMENT_AMOUNT+r.FINE_AMOUNT as amount,a.name as disname from REPAYMENT_IMPORT r " + " LEFT JOIN mortgage e on r.mortgage_ID = e.ID left join estate e1 on e.estate_id = e1.id " + " LEFT JOIN AREA a on a.area_ID = e1.city_id" + " left join area b on b.area_id = e1.district_id" + " where 1 = 1 " + pubsql + " and e1.estate_status_id = (select id from data_dict where code = '1' and class = 'com.cindata.dictionary.EstateStatusDict')) t1 group  by t1.disname";
            }

        }
        else if (key == "2")
        {
            //房贷比
            sql = "select " + "count(case when t1.rate >0 and t1.rate<= 0.2 then 1 else null end) as count1," + "count(case when t1.rate > 0.2 and t1.rate<= 0.4 then 1 else null end) as count2," + "count(case when t1.rate > 0.4 and t1.rate<= 0.6 then 1 else null end) as count3," + "count(case when t1.rate > 0.6 and t1.rate<= 0.8 then 1 else null end) as count4," + "count(case when t1.rate > 0.8 and t1.rate<= 1 then 1 else null end) as count5," + "sum(case when t1.rate >0 and t1.rate<= 0.2 then t1.amount else 0 end) as sum1," + "sum(case when t1.rate >0.2 and t1.rate<= 0.4 then t1.amount else 0 end) as sum2," + "sum(case when t1.rate >0.4 and t1.rate<= 0.6 then t1.amount else 0 end) as sum3," + "sum(case when t1.rate >0.6 and t1.rate<= 0.8 then t1.amount else 0 end) as sum4," + "sum(case when t1.rate >0.8 and t1.rate<= 1 then t1.amount else 0 end) as sum5" + "  from (" + "SELECT r.ID,r.SHOULD_REPAYMENT_AMOUNT+r.FINE_AMOUNT as amount ,e.LTV as rate from REPAYMENT_IMPORT r " + " LEFT JOIN mortgage e on r.mortgage_ID = e.ID  left join estate e1 on e.estate_id = e1.id " + " left join area a on a.area_id = e1.city_id" + " left join area b on b.area_id= e1.district_id" + " where 1 = 1 " + pubsql + " and e1.estate_status_id = (select id from data_dict where code = '1' and class = 'com.cindata.dictionary.EstateStatusDict') )t1";

        }
        else if (key == "3")
        {
            //贷款时年龄
            sql = "select " + "count(case when t1.age<= 25 then 1 else null end) as count1," + "count(case when t1.age >= 26 and t1.age<= 35 then 1 else null end) as count2," + "count(case when t1.age >= 36 and t1.age<= 45 then 1 else null end) as count3," + "count(case when t1.age >= 46 and t1.age<= 55 then 1 else null end) as count4," + "count(case when t1.age >= 55 then 1 else null end) as count5," + "sum(case when t1.age <=25 then t1.amount else 0 end) as sum1," + "sum(case when t1.age >=26 and t1.age<= 35 then t1.amount else 0 end) as sum2," + "sum(case when t1.age >=36 and t1.age<= 45 then t1.amount else 0 end) as sum3," + "sum(case when t1.age >=46 and t1.age<= 55 then t1.amount else 0 end) as sum4," + "sum(case when t1.age >=56 and t1.age<= 1 then t1.amount else 0 end) as sum5" + "  from (" + "SELECT r.ID,r.SHOULD_REPAYMENT_AMOUNT+r.FINE_AMOUNT as amount , " + "trunc(months_between(e.start_date, e.BORR_BIRTHDAY )/12) as age  from REPAYMENT_IMPORT r " + " LEFT JOIN mortgage e on r.mortgage_ID = e.ID  left join estate e1 on e.estate_id = e1.id " + " left join area a on a.area_id = e1.city_id" + " left join area b on b.area_id= e1.district_id" + " where 1 = 1 " + pubsql + " and e1.estate_status_id = (select id from data_dict where code = '1' and class = 'com.cindata.dictionary.EstateStatusDict')) t1";
        }
        else if (key == "4")
        {
            //还款时间
            sql = "select " + "count(case when t1.year >=0 and t1.year< 1 then 1 else null end) as count1," + "count(case when t1.year >=1 and t1.year< 2 then 1 else null end) as count2," + "count(case when t1.year >= 2 and t1.year< 3 then 1 else null end) as count3," + "count(case when t1.year >= 3 and t1.year< 4 then 1 else null end) as count4," + "count(case when t1.year >= 4 and t1.year< 5 then 1 else null end) as count5," + "count(case when t1.year >= 5 and t1.year<10 then 1 else null end) as count6," + "count(case when t1.year >= 10  then 1 else null end) as count7," + "sum(case when t1.year >=0 and t1.year< 1 then t1.amount else 0 end) as sum1," + "sum(case when t1.year >=1 and t1.year< 2 then t1.amount else 0 end) as sum2," + "sum(case when t1.year >=2 and t1.year< 3 then t1.amount else 0 end) as sum3," + "sum(case when t1.year >=3 and t1.year< 4 then t1.amount else 0 end) as sum4," + "sum(case when t1.year >=4 and t1.year< 5 then t1.amount else 0 end) as sum5," + "sum(case when t1.year >=5 and t1.year< 6 then t1.amount else 0 end) as sum6," + "sum(case when t1.year >=10 then t1.amount else 0 end) as sum7" + "  from (" + "SELECT r.ID,r.SHOULD_REPAYMENT_AMOUNT+r.FINE_AMOUNT as amount , " + "trunc(months_between(r.REPAYMENT_DATE, e.START_DATE )/12) as year  from REPAYMENT_IMPORT r " + " LEFT JOIN Mortgage e on r.mortgage_ID = e.ID  left join estate e1 on e.estate_id = e1.id " + " left join area a on a.area_id = e1.city_id" + " left join area b on b.area_id= e1.district_id" + " where 1 = 1 " + pubsql + " and e1.estate_status_id = (select id from data_dict where code = '1' and class = 'com.cindata.dictionary.EstateStatusDict') ) t1"
        }
        println(sql)
        //执行sql
        result = db.rows(sql).asList();

        //对结果进行结构统一处理
        if (key == "1" || key == null)
        {
            result.each {
                resultList.add(["counts": it.counts, "amounts": it.amounts, "ynames": it.disname])
            }
        }
        else if (key == "2")
        {
            result.each {
                resultList.add(["counts": result[0].count1, "amounts": result[0].sum1 == null ? 0 : result[0].sum1, "ynames": "0~20%"])
                resultList.add(["counts": result[0].count2, "amounts": result[0].sum2 == null ? 0 : result[0].sum2, "ynames": "20~40%"])
                resultList.add(["counts": result[0].count3, "amounts": result[0].sum3 == null ? 0 : result[0].sum3, "ynames": "40~60%"])
                resultList.add(["counts": result[0].count4, "amounts": result[0].sum4 == null ? 0 : result[0].sum4, "ynames": "60~80%"])
                resultList.add(["counts": result[0].count5, "amounts": result[0].sum5 == null ? 0 : result[0].sum5, "ynames": "80~100%"])
            }
        }
        else if (key == "3")
        {
            result.each {
                resultList.add(["counts": result[0].count1, "amounts": result[0].sum1 == null ? 0 : result[0].sum1, "ynames": "25岁以下"])
                resultList.add(["counts": result[0].count2, "amounts": result[0].sum2 == null ? 0 : result[0].sum2, "ynames": "26到35岁"])
                resultList.add(["counts": result[0].count3, "amounts": result[0].sum3 == null ? 0 : result[0].sum3, "ynames": "36到45岁"])
                resultList.add(["counts": result[0].count4, "amounts": result[0].sum4 == null ? 0 : result[0].sum4, "ynames": "46到55岁"])
                resultList.add(["counts": result[0].count5, "amounts": result[0].sum5 == null ? 0 : result[0].sum5, "ynames": "55岁以上"])
            }
        }
        else if (key == "4")
        {
            result.each {
                resultList.add(["counts": result[0].count1, "amounts": result[0].sum1 == null ? 0 : result[0].sum1, "ynames": "小于一年"])
                resultList.add(["counts": result[0].count2, "amounts": result[0].sum2 == null ? 0 : result[0].sum2, "ynames": "1-2年"])
                resultList.add(["counts": result[0].count3, "amounts": result[0].sum3 == null ? 0 : result[0].sum3, "ynames": "2-3年"])
                resultList.add(["counts": result[0].count4, "amounts": result[0].sum4 == null ? 0 : result[0].sum4, "ynames": "3-4年"])
                resultList.add(["counts": result[0].count5, "amounts": result[0].sum5 == null ? 0 : result[0].sum5, "ynames": "4-5年"])
                resultList.add(["counts": result[0].count6, "amounts": result[0].sum6 == null ? 0 : result[0].sum6, "ynames": "5-10年"])
                resultList.add(["counts": result[0].count7, "amounts": result[0].sum7 == null ? 0 : result[0].sum7, "ynames": "10年以上"])
            }

        }
        println resultList
        render([resultList: resultList, status: status] as JSON)
    }

    /************************************报表-于鹏杰********************************************/
    /**
     * 放款情况报表 （机构过滤）*/
    @Transactional
    def loanSituationReport()
    {
        def max = params['max']
        def order = params["order"]
        def sort = params["sort"]
        //放款编号
        def serialNumber = params["serialNumber"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result;
        def sql = "from MortgageCollateral e where 1 = 1  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.account.id in " + accountSql
        }
        if (serialNumber && serialNumber != "")
        {
            sql += " and e.mortgage.serialNumber= '" + serialNumber + "'"
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.mortgage.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.mortgage.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        //数量统计
        def countsql = "select count(1) " + sql;
        def count = Mortgage.executeQuery(countsql);
        if (order && sort)
        {
            sql += " order by e.mortgage.${sort} ${order}"
        }
        else
        {
            sql += " order by e.mortgage.id desc "
        }

        result = Mortgage.executeQuery(sql, [max: params.max ?: 10, offset: params.offset ?: 0]).asList();
        respond result, model: [estateList: result, estateCount: count[0], params: params], view: 'loanSituationReport'
    }

    /**
     * 放款情况报表导出下载 （机构过滤）*/
    @Transactional
    def loanSituationReportExportExcel()
    {
        //放款编号
        def contractNo = params["contractNo"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        def result;
        def sql = "from MortgageCollateral e where 1 = 1  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.account.id in " + accountSql
        }
        if (contractNo && contractNo != "")
        {
            sql += " and e.mortgage.contractNo= '" + contractNo + "'"
            // sql += " and e.contractNo= " + contractNo
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.mortgage.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.mortgage.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        result = Mortgage.executeQuery(sql).asList();

        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("放款情况报表", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()
        //放款情况报表
        String[] headers = ["贷款编号", "提前放款", "借款人姓名", "证件号码", "公积金借款金额(元)", "商业借款金额(元)", "放款日期", "放款银行",
            "房屋类型", "原始借款期数(月)", "行政区", "押品地址", "成交总价(元)", "建筑面积", "受理网点", "担保机构", "担保方式"];
        String[] params = ["contractNo", "loanMethod", "borrName", "borrIdCardNo", "publicFundLoansAmount", "bankLoansAmount", "startDate", "loansBankName",
            "estateType", "loansDuration", "district", "sourceAddress", "totalPrice", "buildArea", "accountName", "guaranteeInstitution", "guaranteeMethod"];
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        result.each {
            Map<String, T> m = new HashMap<String, T>();
            m.put("contractNo", it.mortgage?.contractNo)
            m.put("loanMethod", it?.mortgage?.loanMethod?.name = '提前放款' ? '是' : '否')
            m.put("borrName", it.mortgage?.borrName)
            m.put("borrIdCardNo", it.mortgage?.borrIdCardNo)
            m.put("publicFundLoansAmount", it.mortgage?.publicFundLoansAmount + "")
            m.put("bankLoansAmount", it.mortgage?.bankLoansAmount + "")
            m.put("startDate", sdf.format(it.mortgage?.startDate))
            m.put("loansBankName", it.mortgage?.loansBankName + "")

            m.put("estateType", it?.mortgage?.estateType?.name + "")
            m.put("loansDuration", it.mortgage?.loansDuration + "")
            m.put("district", it?.collateral?.district?.name)
            m.put("sourceAddress", it?.collateral?.estate?.sourceAddress + "")
            m.put("totalPrice", it.mortgage?.totalPrice)
            m.put("buildArea", it?.collateral?.estate?.buildArea)
            m.put("accountName", it.mortgage?.accountName)
            m.put("guaranteeInstitution", it?.mortgage?.guaranteeInstitution?.name)
            m.put("guaranteeMethod", it?.mortgage?.guaranteeMethod?.name + "")
            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("放款情况报表", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
    }

    /**
     * 还款情况报表 (机构过滤)*/
    @Transactional
    def paymentSituationReport()
    {
        def max = params['max']
        def order = params["order"]
        def sort = params["sort"]
        //贷款编号
        def serialNumber = params["serialNumber"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result;
        def sql = "from MortgageCollateral e where 1 = 1  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.account.id in " + accountSql
        }
        if (serialNumber && serialNumber != "")
        {
            sql += " and e.mortgage.serialNumber= '" + serialNumber + "'"
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.mortgage.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.mortgage.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        //数量统计
        def countsql = "select count(1) " + sql;
        def count = Mortgage.executeQuery(countsql);
        if (order && sort)
        {
            sql += " order by e.mortgage.${sort} ${order}"
        }
        else
        {
            sql += " order by e.mortgage.id desc "
        }
        result = Mortgage.executeQuery(sql, [max: params.max ?: 10, offset: params.offset ?: 0]).asList();
        respond result, model: [estateList: result, estateCount: count[0], params: params], view: 'paymentSituationReport'
    }

    /**
     * 还款情况报表数据导出下载 （机构过滤）*/
    @Transactional
    def paymentSituationReportExportExcel()
    {
        //放款编号
        def contractNo = params["contractNo"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        def result;
        def sql = "from MortgageCollateral e where 1 = 1  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.account.id in " + accountSql
        }
        if (contractNo && contractNo != "")
        {
            sql += " and e.mortgage.contractNo= '" + contractNo + "'"
            // sql += " and e.contractNo= " + contractNo
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.mortgage.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.mortgage.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(e.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        result = Mortgage.executeQuery(sql).asList();

        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("还款情况报表", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()
        //还款情况报表
        String[] headers = ["贷款编号", "提前放款", "借款人姓名", "证件号码", "公积金借款金额(元)", "商业借款金额(元)", "放款日期", "放款银行",
            "房屋类型", "原始借款期数(月)", "行政区", "押品地址", "回收总本金(元)", "正常还款总本金(元)", "逾期还款总本金(元)",
            "提前还款总本金(元)", "总回收利息-不含罚息(元)", "总回收罚息(元)", "扣款日期", "已供款期数-不含转逾期(月)",
            "受理网点", "担保机构", "担保方式"];
        String[] params = ["contractNo", "loanMethod", "borrName", "borrIdCardNo", "publicFundLoansAmount", "bankLoansAmount", "startDate", "loansBankName",
            "estateType", "loansDuration", "district", "sourceAddress", "curRecycleTotalCapital", "curNormalTotalCapital", "curOverdueTotalCapital",
            "curInAdvanceTotalCapital", "curRecycleTotalInterest", "curRecycleTotalFine", "lastPayAt", "curHasPaymentDuration",
            "accountName", "guaranteeInstitution", "guaranteeMethod"];
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        result.each {
            Map<String, T> m = new HashMap<String, T>();
            m.put("contractNo", it.mortgage?.contractNo)
            m.put("loanMethod", it?.mortgage?.loanMethod?.name = '提前放款' ? '是' : '否')
            m.put("borrName", it.mortgage?.borrName + "")
            m.put("borrIdCardNo", it.mortgage?.borrIdCardNo + "")
            m.put("publicFundLoansAmount", it.mortgage?.publicFundLoansAmount + "")
            m.put("bankLoansAmount", it.mortgage?.bankLoansAmount + "")
            m.put("startDate", sdf.format(it.mortgage?.startDate))
            m.put("loansBankName", it.mortgage?.loansBankName + "")

            m.put("estateType", it?.mortgage?.estateType?.name + "")
            m.put("loansDuration", it.mortgage?.loansDuration + "")
            m.put("district", it?.collateral?.district?.name + "")
            m.put("sourceAddress", it?.collateral?.estate?.sourceAddress + "")
            m.put("curRecycleTotalCapital", it.mortgage?.curRecycleTotalCapital + "")
            m.put("curNormalTotalCapital", it.mortgage?.curNormalTotalCapital + "")
            m.put("curOverdueTotalCapital", it.mortgage?.curOverdueTotalCapital + "")

            m.put("curInAdvanceTotalCapital", it.mortgage?.curInAdvanceTotalCapital + "")
            m.put("curRecycleTotalInterest", it.mortgage?.curRecycleTotalInterest + "")
            m.put("curRecycleTotalFine", it.mortgage?.curRecycleTotalFine + "")
            m.put("lastPayAt", it.mortgage?.lastPayAt != null ? sdf.format(it.mortgage?.lastPayAt) : "")
            m.put("curHasPaymentDuration", it.mortgage?.curHasPaymentDuration + "")

            m.put("accountName", it.mortgage?.accountName + "")
            m.put("guaranteeInstitution", it?.mortgage?.guaranteeInstitution?.name)
            m.put("guaranteeMethod", it?.mortgage?.guaranteeMethod?.name + "")
            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("还款情况报表", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
    }

    /**
     * 逾期情况报表 （机构过滤）*/
    @Transactional
    def overdueSituationReport()
    {
        def max = params['max']
        def order = params["order"]
        def sort = params["sort"]
        //放款编号
        def serialNumber = params["serialNumber"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result;
        def sql = " from RepaymentImport r where r.repaymentType.name = '逾期还款' or r.repaymentType.name = '转逾期'    ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and r.mortgage.collateral.estate.account.id in " + accountSql
        }
        if (serialNumber && serialNumber != "")
        {
            sql += " and r.mortgage.serialNumber= '" + serialNumber + "'"
        }
        if (sourceAddress)
        {
            sql += " and r.mortgage.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and r.mortgage.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and r.mortgage.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(r.overdueDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(r.overdueDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }

        //  def db = new groovy.sql.Sql(dataSource)        //数量统计
        def countsql = "select count(1) " + sql;
        def count = RepaymentImport.executeQuery(countsql)
        if (order && sort)
        {
            sql += " order by r.mortgage.collateral.estate.${sort} ${order}"
        }
        else
        {
            sql += " order by r.mortgage.collateral.estate.id desc "
        }
        result = RepaymentImport.executeQuery("select  r " + sql, [max: params.max ?: 10, offset: params.offset ?: 0]).asList();
        respond result, model: [estateList: result, estateCount: count[0], params: params], view: 'overdueSituationReport'
    }

    /**
     * 逾期情况报表数据导出下载 (机构过滤)*/
    @Transactional
    def overdueSituationReportExportExcel()
    {
        //放款编号
        def contractNo = params["contractNo"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        def result;
        def sql = "select r from RepaymentImport r, com.cindata.Mortgage e where  r.mortgage = e.id  and (r.repaymentType.name = '逾期还款' or r.repaymentType.name = '转逾期')  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.estate.account.id in " + accountSql
        }
        if (contractNo && contractNo != "")
        {
            sql += " and e.contractNo= '" + contractNo + "'"
            // sql += " and e.contractNo= " + contractNo
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(r.overdueDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(r.overdueDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        result = RepaymentImport.executeQuery(sql).asList();

        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("逾期情况报表", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()
        //逾期情况报表
        String[] headers = ["贷款编号", "提前放款", "借款人姓名", "证件号码", "放款日期", "房屋类型",
            "行政区", "押品地址", "本金金额（元）", "利息金额（元）", "罚息金额（元）",
            "逾期日期", "还逾期日期", "逾期状态", "受理网点", "担保机构", "担保方式"];
        String[] params = ["contractNo", "loanMethod", "borrName", "borrIdCardNo", "startDate", "estateType",
            "districtName", "sourceAddress", "practicalRepaymentCapital", "practicalRepaymentInterest", "fineAmount",
            "overdueDate", "practicalChargeBackDate", "overdueState", "accountName", "guaranteeInstitution", "guaranteeMethod"];
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        result.each {
            Map<String, T> m = new HashMap<String, T>();
            m.put("contractNo", it?.mortgage?.contractNo)
            m.put("loanMethod", it?.mortgage?.loanMethod?.name = '提前放款' ? '是' : '否')
            m.put("borrName", it?.mortgage?.borrName + "")
            m.put("borrIdCardNo", it?.mortgage?.borrIdCardNo + "")
            m.put("startDate", sdf.format(it?.mortgage?.startDate))
            m.put("estateType", it?.mortgage?.estateType?.name + "")
            m.put("districtName", it?.mortgage?.estate?.district?.name + "")
            m.put("sourceAddress", it?.mortgage?.estate?.sourceAddress + "")
            m.put("practicalRepaymentCapital", it.practicalRepaymentCapital + "")
            m.put("practicalRepaymentInterest", it.practicalRepaymentInterest + "")
            m.put("fineAmount", it.fineAmount + "")
            m.put("overdueDate", it.overdueDate != null ? sdf.format(it.overdueDate) : "")
            m.put("practicalChargeBackDate", it.practicalChargeBackDate != null ? sdf.format(it.practicalChargeBackDate) : "")
            m.put("overdueState", it?.overdueState?.name)
            m.put("accountName", it?.mortgage?.accountName)
            m.put("guaranteeInstitution", it?.mortgage?.guaranteeInstitution?.name)
            m.put("guaranteeMethod", it?.mortgage?.guaranteeMethod?.name + "")
            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("逾期情况报表", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
    }

    /**
     * 提前还款情况报表（机构过滤）*/
    @Transactional
    def advancePaymentSituationReport()
    {
        def max = params['max']
        def order = params["order"]
        def sort = params["sort"]
        //放款编号
        def serialNumber = params["serialNumber"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        def result;
        def sql = " from RepaymentImport r , com.cindata.Mortgage e where r.mortgage = e.id and r.repaymentType.name = '提前还款' ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.estate.account.id in " + accountSql
        }
        if (serialNumber && serialNumber != "")
        {
            sql += " and e.serialNumber= '" + serialNumber + "'"
        }
        if (sourceAddress)
        {
            sql += " and e.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(r.practicalChargeBackDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(r.practicalChargeBackDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }

        //  def db = new groovy.sql.Sql(dataSource)        //数量统计
        def countsql = "select count(1) " + sql;
        def count = RepaymentImport.executeQuery(countsql).asList()
        if (order && sort)
        {
            sql += " order by e.${sort} ${order}"
        }
        else
        {
            sql += " order by e.id desc "
        }
        result = RepaymentImport.executeQuery("select r " + sql, [max: params.max ?: 10, offset: params.offset ?: 0]).asList();
        respond result, model: [estateList: result, estateCount: count[0], params: params], view: 'advancePaymentSituationReport'
    }

    /**
     * 提前还款情况报表导出下载 （机构过滤）*/
    @Transactional
    def advancePaymentSituationReportExportExcel()
    {
        //放款编号
        def contractNo = params["contractNo"];
        //押品地址
        def sourceAddress = params["sourceAddress"];
        //担保机构
        def jigou = params["jigou"];
        //放款开始日期
        def startDateStart = params["startDateStart"];
        //放款结束日期
        def startDateEnd = params["startDateEnd"];
        //担保方式
        def method = params["method"];
        def result;
        def sql = "select r from RepaymentImport r , com.cindata.Mortgage e where r.mortgage = e.id and r.repaymentType.name = '提前还款'  ";
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        if (accountSql)
        {
            sql += " and e.collateral.estate.account.id in " + accountSql
        }
        if (contractNo && contractNo != "")
        {
            sql += " and e.contractNo= '" + contractNo + "'"
            // sql += " and e.contractNo= " + contractNo
        }
        if (sourceAddress)
        {
            sql += " and e.collateral.estate.sourceAddress like '%" + sourceAddress + "%'"
        }
        if (jigou && jigou != "-1")
        {
            sql += " and e.guaranteeInstitution = " + jigou
        }
        if (method && method != "-1")
        {
            sql += " and e.guaranteeMethod = " + method
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += "and to_char(r.practicalChargeBackDate,'YYYY-MM-DD HH24:MI:SS') >  '${dates1}'"
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(r.practicalChargeBackDate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        result = RepaymentImport.executeQuery(sql).asList();

        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("提前还款情况报表", "UTF-8") + ".xls")
        response.contentType = "application/x-rarx-rar-compressed"
        ExcelExportService excelExportService = new ExcelExportService()
        //提前还款情况报表
        String[] headers = ["贷款编号", "提前放款", "借款人姓名", "公积金借款金额(元)", "商业借款金额(元)", "放款日期",
            "提前还款类型", "提前还款额（元）", "缩期前剩余期数（月）", "缩期后剩余期数（月）", "扣款日期",
            "还款方式", "房屋类型", "行政区", "押品地址", "受理网点", "担保机构", "担保方式"];
        String[] params = ["contractNo", "loanMethod", "borrName", "publicFundLoansAmount", "bankLoansAmount", "startDate",
            "prepaymentType", "practicalRepaymentAmount", "beforePrepaymentDuration", "afterPrepaymentDuration", "practicalChargeBackDate",
            "paymethod", "estateType", "district", "sourceAddress", "accountName", "guaranteeInstitution", "guaranteeMethod"];
        List<Map<String, T>> listn = new ArrayList<Map<String, T>>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        result.each {
            Map<String, T> m = new HashMap<String, T>();
            m.put("contractNo", it.mortgage.contractNo)
            m.put("loanMethod", it?.mortgage?.loanMethod?.name = '提前放款' ? '是' : '否')
            m.put("borrName", it?.mortgage?.borrName + "")
            m.put("publicFundLoansAmount", it?.mortgage?.publicFundLoansAmount + "")
            m.put("bankLoansAmount", it?.mortgage?.bankLoansAmount + "")
            m.put("startDate", sdf.format(it?.mortgage?.startDate))

            m.put("prepaymentType", it.prepaymentType?.name + "")
            m.put("practicalRepaymentAmount", it.practicalRepaymentAmount + "")
            m.put("beforePrepaymentDuration", it.beforePrepaymentDuration + "")
            m.put("afterPrepaymentDuration", it.afterPrepaymentDuration + "")
            m.put("practicalChargeBackDate", sdf.format(it?.practicalChargeBackDate) + "")
            m.put("paymethod", it?.mortgage?.curRepaymentMode?.name + "")
            m.put("estateType", it?.mortgage?.estateType?.name + "")
            m.put("district", it?.mortgage?.estate?.district?.name + "")
            m.put("sourceAddress", it?.mortgage?.estate?.sourceAddress + "")
            m.put("accountName", it?.mortgage?.accountName + "")
            m.put("guaranteeInstitution", it?.mortgage?.guaranteeInstitution?.name)
            m.put("guaranteeMethod", it?.mortgage?.guaranteeMethod?.name + "")
            listn.add(m)
        }
        HSSFWorkbook workbook = excelExportService.exportExcel("提前还款情况报表", headers, listn, params);
        workbook.write(response.getOutputStream());
        response.flushBuffer() //立即清空输出
        response.getOutputStream().close();
        println("下载完成")
    }

    /**
     * 交易面积排行数据请求*/
    @Transactional
    def tradingAreaRanking()
    {
        respond(view: "tradingAreaRanking")
    }

    /******************交易面积排行数据请求**************************/
    def tradingAreaRankingDate()
    {
        def max = params['max']
        def use = params['use']
        //使用用途 全部/住宅
        def city = params['city']
        def district = params['district']
        //建成年份开始
        def buildyearStart = params["buildyearStart"]
        //建成年份结束
        def buildyearEnd = params["buildyearEnd"]
        //交易开始时间
        def contractdateStart = params["contractdateStart"]
        //交易结束时间
        def contractdateEnd = params["contractdateEnd"]
        //建筑面积开始
        def buildingareaStart = params["buildingareaStart"]
        //建筑面积结束
        def buildingareaEnd = params["buildingareaEnd"]
        def sql = " select new map ( s.communityName as communityName,s.userReal as userReal," + " s.community.id as communityId ,s.buildyear as buildyear, sum(s.buildingarea) as value ) " + " from Sale s where 1 = 1 "
        //按城市查询
        if (city && city != '-1')
        {
            sql += " and s.city.zoneNumber = '${city}'"
        }
        //按行政区查询
        if (district && district != '-1')
        {
            sql += " and s.district.zoneNumber = '${district}'"
        }
        //按用途查询
        if (use)
        {
            sql += " and s.userReal = '${use}'"
        }
        //建筑年份开始
        if (buildyearStart)
        {
            sql += " and s.buildyear >= '${buildyearStart}' "
        }
        //建筑年份结束
        if (buildyearEnd)
        {
            sql += " and s.buildyear <= '${buildyearStart}' "
        }
        //交易开始时间
        if (contractdateStart)
        {
            def dates = contractdateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(s.contractdate,'YYYY-MM-DD HH24:MI:SS')>='${dates1}'  "
        }
        //交易结束时间
        if (contractdateEnd)
        {
            def datee = contractdateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += "  and  to_char(s.contractdate,'YYYY-MM-DD HH24:MI:SS') <='${datee1}' "
        }
        //建筑面积开始
        if (buildingareaStart && buildingareaStart != '')
        {
            sql += " and s.buildingarea >= " + buildingareaStart
        }
        //建筑面积结束
        if (buildingareaEnd && buildingareaEnd != '')
        {
            sql += " and s.buildingarea <= " + buildingareaEnd
        }
        if (max)
        {
            params.max = max
        }
        else
        {
            params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        }
        params.offset = params.offset ? params.offset.toInteger() : 0

        sql += " group by s.communityName ,s.community.id ,s.buildyear,s.userReal order by  value desc "
        def areaList = Estate.executeQuery(sql, [max: params.max, offset: params.offset]).asList()
        render([result: areaList] as JSON)
    }

    /**
     * 公用条件查询-张成远*/
    def pubConditionSql(params)
    {
        //城市
        def city = params['city']
        //行政区
        def district = params['district']
        //贷款余额上限
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款金额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //放款时间开始
        def startDateStart = params["startDateStart"]
        //放款时间结束
        def startDateEnd = params["startDateEnd"]
        //面积上限
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //贷款到期日开始
        def endDateStart = params["endDateStart"]
        //贷款到期日结束
        def endDateEnd = params["endDateEnd"]
        //贷款时长
        def loansDuration = params["loansDuration"]
        //风险
        def risk = params["risk"]
        def sql = ""
        //查询条件
        if (city && city != '-1')
        {
            sql += " and m.collateral.city.zoneNumber ='${city}'"
        }
        if (district && district != '-1')
        {
            sql += " and m.collateral.district.zoneNumber = '${district}'"
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and m.mortgage.loansBalance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and m.mortgage.loansBalance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and m.mortgage.loansAmount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and m.mortgage.loansAmount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart && startDateStart != '')
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(m.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (startDateEnd && startDateEnd != '')
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(m.mortgage.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and m.collateral.sourceBuildArea >= '${buildAreaStart}'"
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and m.collateral.sourceBuildArea <= '${buildAreaEnd}'"
        }
        if (endDateStart && endDateStart != '')
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(m.mortgage.endDate,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (endDateEnd && endDateEnd != '')
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(m.mortgage.endDate,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def minValue = it.minValue
                    def maxValue = it.maxValue
                    sql += " and m.mortgage.loansDuration > '${minValue}' and m.mortgage.loansDuration <= '${maxValue}'"
                }
            }
        }
        if (risk && risk != '')
        {
            def riskDimension = RiskDimension.findByLabel(risk)
            def minValue = riskDimension.minValue / 100
            def maxValue = riskDimension.maxValue / 100
            sql += " and m.mortgage.cltv > '${minValue}' and m.mortgage.cltv <= '${maxValue}'"
        }
        return sql
    }

    /**
     * 公用条件查询-张成远*/
    def pubConditionNativeSql(params)
    {
        //城市
        def city = params['city']
        //行政区
        def district = params['district']
        //贷款余额上限
        def loansBalanceStart = params["loansBalanceStart"]
        //贷款余额下限
        def loansBalanceEnd = params["loansBalanceEnd"]
        //贷款金额上限
        def loansAmountStart = params["loansAmountStart"]
        //贷款金额下限
        def loansAmountEnd = params["loansAmountEnd"]
        //放款时间开始
        def startDateStart = params["startDateStart"]
        //放款时间结束
        def startDateEnd = params["startDateEnd"]
        //面积上限
        def buildAreaStart = params["buildAreaStart"]
        //面积下限
        def buildAreaEnd = params["buildAreaEnd"]
        //贷款到期日开始
        def endDateStart = params["endDateStart"]
        //贷款到期日结束
        def endDateEnd = params["endDateEnd"]
        //贷款时长
        def loansDuration = params["loansDuration"]
        //风险
        def risk = params["risk"]
        def sql = ""
        //查询条件
        if (city && city != '-1')
        {
            sql += " and a.zone_number = '${city}'"
        }
        if (district && district != '-1')
        {
            sql += " and b.zone_number = '${district}'"
        }
        if (loansBalanceStart && loansBalanceStart != '')
        {
            sql += " and m.loans_balance >= ${loansBalanceStart}*10000 "
        }
        if (loansBalanceEnd && loansBalanceEnd != '')
        {
            sql += " and m.loans_balance <= ${loansBalanceEnd}*10000 "
        }
        if (loansAmountStart && loansAmountStart != '')
        {
            sql += " and m.loans_amount >= ${loansAmountStart}*10000 "
        }
        if (loansAmountEnd && loansAmountEnd != '')
        {
            sql += " and m.loans_amount <= ${loansAmountEnd}*10000 "
        }
        if (startDateStart && startDateStart != '')
        {
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(m.start_date,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (startDateEnd && startDateEnd != '')
        {
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(m.start_date,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (buildAreaStart && buildAreaStart != '')
        {
            sql += " and e.source_build_area >= '${buildAreaStart}'"
        }
        if (buildAreaEnd && buildAreaEnd != '')
        {
            sql += " and e.source_build_area <= '${buildAreaEnd}'"
        }
        if (endDateStart && endDateStart != '')
        {
            def dates = endDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            sql += " and to_char(m.end_date,'YYYY-MM-DD HH24:MI:SS') >= '${dates1}'"
        }
        if (endDateEnd && endDateEnd != '')
        {
            def datee = endDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            sql += " and to_char(m.end_date,'YYYY-MM-DD HH24:MI:SS') <= '${datee1}'"
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def minValue = it.minValue
                    def maxValue = it.maxValue
                    sql += " and m.loans_duration > '${minValue}' and m.loans_duration <= '${maxValue}'"
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    sql += " and m.cltv > '${minValue}' and m.cltv <= '${maxValue}'"
                }

            }
        }
        return sql
    }

    // 分析报告
    def report(Integer max)
    {
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        params.offset = params.offset ? params.offset.toInteger() : 0

        def user = User.findById(springSecurityService.principal.id)
        def accountName = user?.account?.name
        def defaultCity = user?.account?.city?.zoneNumber
        def zoneNumber = params["city"] ?: defaultCity
        def gtype = params['gtype']

        def sql1 = "select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName) from Estate e where e.estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.zoneNumber = '${zoneNumber}' group by e.district.id order by counts desc"
        def estateList = Estate.executeQuery(sql1).asList()
        def districtCounts = estateList?.size()
        def estateCounts = 0
        estateList?.each {
            if (it?.counts && it?.counts > 0)
            {
                estateCounts += it?.counts
            }
        }

        def districtName1 = estateList[0]?.districtName
        def districtName2 = estateList[1]?.districtName
        def districtName3 = estateList[2]?.districtName
        def districtEstateCounts1 = estateList[0]?.counts ? estateList[0]?.counts : 0
        def districtEstateCounts2 = estateList[1]?.counts ? estateList[1]?.counts : 0
        def districtEstateCounts3 = estateList[2]?.counts ? estateList[2]?.counts : 0

        def list = []

        def sql2 = "select new map(count(e.id) as counts, e.communityId as communityId, e.communityName as community, (select a.name from Area a where id = e.district.id) as district, (select b.name from Area b where id = e.city.id) as city) from Estate e where estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.zoneNumber = '${zoneNumber}' group by e.communityId, e.communityName, e.district.id, e.city.id ORDER BY counts desc, communityName"
        def estateList2 = Estate.executeQuery(sql2).asList()
        def communityCounts = estateList2?.size()

        def c1 = estateList2[0]?.community
        def d1 = estateList2[0]?.district
        def t1 = estateList2[0]?.counts ? estateList2[0]?.counts : 0

        def c2 = estateList2[1]?.community
        def d2 = estateList2[1]?.district
        def t2 = estateList2[1]?.counts ? estateList2[1]?.counts : 0

        def top20CommunityList = []
        for (
            def i = 0;
                i < 20;
                i++)
        {
            if (estateList2[i])
            {
                top20CommunityList.add(estateList2[i])
            }
        }

        // 押品剩余还款期分布
        def db = new groovy.sql.Sql(dataSource)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        def cityId = City.findByZoneNumber(zoneNumber)?.id
        def estateStatusId = EstateStatusDict.findByName('在押')?.id
        def sql3 = "select '【0-5】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 0 and remain_time <=60 UNION "
        sql3 += "select '【5-10】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 60 and remain_time <= 120 UNION "
        sql3 += "select '【10-15】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 120 and remain_time <=180 UNION "
        sql3 += "select '【15-20】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 180 and remain_time <=240 UNION "
        sql3 += "select '【20年以上】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 240 ORDER BY counts desc"
        def remainTimeList = db.rows(sql3).asList()
        db.close()

        // 押品剩余还款期分布
        def sql4 = "select new map(count(re.id) as overnumbers,"
        sql4 += " (select communityName from Estate where id = re.mortgage.id) as communityName, "
        sql4 += " (select communityId from Estate where id = re.mortgage.id) as communityId, "
        sql4 += " (select account.name from Estate where id = re.mortgage.id) as accountName, "
        sql4 += " (select externalId from Mortgage where id = re.mortgage.id) as externalId, "
        sql4 += " re.mortgage.id as mortgageId)"
        sql4 += " FROM RepaymentImport re LEFT JOIN re.mortgage m LEFT JOIN m.collateral.estate e1"
        sql4 += " WHERE re.continuousOverdueNumber > 0 AND re.overdueState.code = 'NoPay'"
        sql4 += " and e1.city.zoneNumber ='${zoneNumber}'"
        sql4 += " group by re.mortgage ORDER BY overnumbers desc"

        String ids = ""
        def overdueCounts = 0
        def overdueList = Estate.executeQuery(sql4).asList()
        if (overdueList?.size() > 0)
        {
            overdueList?.each {
                overdueCounts += it?.overnumbers
                ids += it?.communityId + ","
            }
            ids = ids?.substring(0, ids?.lastIndexOf(","))
        }

        def standardPrice = apiService.standardPrice(zoneNumber, ids)
        def standardPriceList = standardPrice['data']
        for (
            def i = 0;
                i < overdueList?.size();
                i++)
        {
            for (
                def j = 0;
                    j < standardPriceList?.size();
                    j++)
            {
                if (overdueList[i]?.communityId?.toInteger() == standardPriceList[j]?.id?.toInteger())
                {
                    overdueList[i].standardPrice = standardPriceList[j].standardPrice
                }
            }
        }

        // 押品重估价值
        def sql5 = "select new map(to_char(m.startDate, 'yyyy') as startDate, SUM(m.loansEvaluateTotalPrice)/100000000 as loanTotalPrice, sum(m.totalPrice)/100000000 as currentTotalPrice, sum(m.totalPrice - m.loansEvaluateTotalPrice) / SUM(m.loansEvaluateTotalPrice) as amplitude)"
        sql5 += " from Mortgage m left join m.collateral.estate e "
        sql5 += " where e.estateStatus.code = '1' and e.city.zoneNumber = '${zoneNumber}' and e.account.id in " + accountSql
        sql5 += " GROUP BY to_char(m.startDate, 'yyyy') ORDER BY startDate desc"


        def loanTotalPrice = 0
        def currentTotalPrice = 0
        def evaluateList = Estate.executeQuery(sql5).asList()
        println "evaluateList:" + evaluateList
        evaluateList?.each {
            if (it?.loanTotalPrice && it?.loanTotalPrice > 0)
            {
                loanTotalPrice += it?.loanTotalPrice
            }
            if (it?.currentTotalPrice && it?.currentTotalPrice > 0)
            {
                currentTotalPrice += it?.currentTotalPrice
            }
        }
        if (!evaluateList[0]?.startDate)
        {
            evaluateList = []
        }

        def cityName = Area.findByZoneNumber(zoneNumber)?.name
        println "cityName: " + cityName
        println "zoneNumber: " + zoneNumber
        println "accountName: " + accountName
        println "districtCounts: " + districtCounts
        println "estateCounts: " + estateCounts
        println "districtName1: " + districtName1
        println "districtName2: " + districtName2
        println "districtName3: " + districtName3
        println "districtEstateCounts1: " + districtEstateCounts1
        println "districtEstateCounts2: " + districtEstateCounts2
        println "districtEstateCounts3: " + districtEstateCounts3
        println "communityCounts: " + communityCounts
        println "c1: " + c1
        println "c2: " + c2
        println "d1: " + d1
        println "d2: " + d2
        println "t1: " + t1
        println "t2: " + t2
        println "top20CommunityList: " + top20CommunityList
        println "remainTimeList: " + remainTimeList
        println "overdueList: " + overdueList
        println "overdueCounts: " + overdueCounts
        println "evaluateList: " + evaluateList
        println "loanTotalPrice: " + loanTotalPrice
        println "currentTotalPrice: " + currentTotalPrice
        println "list: " + list

        // respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view:'report'
        if (gtype == "999")
        {
            respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view: 'reportPrinting'
        }
        else
        {
            respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view: 'report'
        }
    }

    // def batchReport(EstateImportBatch estateImportBatch)
    // {
    //     def estateImportList = EstateImport.findAllByBatch(estateImportBatch)
    //     def counts = estateImportList?.size()
    //     def estateList = []
    //     def highRiskCounts = 0
    //     def highRiskUpCounts = 0
    //     def highRiskDownCounts = 0
    //     def netValue = 0
    //     def riskWarningConfigure = RiskWarningConfigure.list()?.get(0)
    //     def valueRiseWeight = riskWarningConfigure.valueRiseWeight        // 价值上升波动范围
    //     def valueDeclineWeight = -riskWarningConfigure.valueDeclineWeight // 价值下降波动范围
    //     estateImportList?.each
    //     {
    //         def estate = Estate.findByExternalId(it?.externalId)
    //         def priceFluctuationsRatio = estate?.priceFluctuationsRatio
    //         if (priceFluctuationsRatio && priceFluctuationsRatio >= valueRiseWeight)
    //         {
    //             highRiskUpCounts++
    //             highRiskCounts++
    //         }
    //         if (priceFluctuationsRatio && priceFluctuationsRatio <= valueDeclineWeight)
    //         {
    //             highRiskDownCounts++
    //             highRiskCounts++
    //         }
    //         def firstEvaluatePrice = estate?.firstEvaluatePrice ?: 0
    //         def latestEvaluatePrice = estate?.latestEvaluatePrice ?: 0
    //         def loansbalance = estate?.loansbalance ?: 0
    //         if (latestEvaluatePrice - firstEvaluatePrice - loansbalance > 0) 
    //         {
    //             netValue++
    //         }
    //         estateList.add(estate)
    //     }

    //     println highRiskCounts
    //     println highRiskUpCounts
    //     println highRiskDownCounts
    //     println netValue
    //     println counts

    //     println estateList

    //     def estateIdSql = ""
    //     if (estateList)
    //     {
    //         String estateIdList = estateList*.id?.toString()
    //         estateIdSql += " (" + estateIdList?.substring(1, estateIdList.lastIndexOf("]")) + ")"
    //     }
    //     // estateList.sort() TODO

    //     def user = User.findById(springSecurityService.principal.id)
    //     def accountName = user?.account?.name
    //     def defaultCity = user?.account?.city?.zoneNumber
    //     def zoneNumber = params["city"] ?: defaultCity
    //     def gtype = params['gtype']

    //     def list = []

    //     // 押品剩余还款期分布
    //     def db = new groovy.sql.Sql(dataSource)
    //     def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
    //     def estateStatusId = EstateStatusDict.findByCode('1')?.id
    //     def sql3 = "select '【0-5】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 0 and remain_time <=60 UNION "
    //     sql3 += "select '【5-10】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 60 and remain_time <= 120 UNION "
    //     sql3 += "select '【10-15】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 120 and remain_time <=180 UNION "
    //     sql3 += "select '【15-20】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 180 and remain_time <=240 UNION "
    //     sql3 += "select '【20年以上】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 240 ORDER BY counts desc"
    //     def remainTimeList = db.rows(sql3).asList()
    //     db.close()

    //     // 押品剩余还款期分布
    //     def sql4 = "select new map(count(re.id) as overnumbers,"
    //     sql4 += " (select communityName from Estate where id = re.mortgage.id) as communityName, "
    //     sql4 += " (select communityId from Estate where id = re.mortgage.id) as communityId, "
    //     sql4 += " (select account.name from Estate where id = re.mortgage.id) as accountName, "
    //     sql4 += " (select externalId from Mortgage where id = re.mortgage.id) as externalId, "
    //     sql4 += " re.mortgage.id as mortgageId)"
    //     sql4 += " FROM RepaymentImport re LEFT JOIN re.mortgage m LEFT JOIN m.collateral.estate e1"
    //     sql4 += " WHERE re.continuousOverdueNumber > 0 AND re.overdueState.code = 'NoPay'"
    //     sql4 += " and e1.id in " + estateIdSql
    //     sql4 += " group by re.mortgage ORDER BY overnumbers desc"

    //     println sql4

    //     String ids = ""
    //     def overdueCounts = 0
    //     def overdueList = Estate.executeQuery(sql4).asList()
    //     if (overdueList?.size() > 0)
    //     {
    //         overdueList?.each {
    //             overdueCounts += it?.overnumbers
    //             ids += it?.communityId + ","
    //         }
    //         ids = ids?.substring(0, ids?.lastIndexOf(","))
    //     }

    //     def standardPrice = apiService.standardPrice(zoneNumber, ids)
    //     def standardPriceList = standardPrice['data']
    //     for (
    //         def i = 0;
    //             i < overdueList?.size();
    //             i++)
    //     {
    //         for (
    //             def j = 0;
    //                 j < standardPriceList?.size();
    //                 j++)
    //         {
    //             if (overdueList[i]?.communityId?.toInteger() == standardPriceList[j]?.id?.toInteger())
    //             {
    //                 overdueList[i].standardPrice = standardPriceList[j].standardPrice
    //             }
    //         }
    //     }

    //     println "list: " + list

    //     // respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view:'report'
    //     // if (gtype == "999")
    //     // {
    //         // respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view: 'reportPrinting'
    //     // }
    //     // else
    //     // {
    //         respond list, model: [batchNumber: estateImportBatch?.batchNumber, netValue: netValue, highRiskCounts: highRiskCounts, highRiskUpCounts: highRiskUpCounts, highRiskDownCounts: highRiskDownCounts, counts: counts, estateList: estateList, estateImportList: estateImportList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts], view: 'batchReport'
    //     // }
    // }

    def batchEstateReport(EstateImportBatch estateImportBatch)
    {
        def batchEstateList = BatchEstate.findAllByBatch(estateImportBatch)
        def counts = batchEstateList?.size()
        def estateList = []
        def highRiskCounts = 0
        def highRiskUpCounts = 0
        def highRiskDownCounts = 0
        def netValue = 0
        def riskWarningConfigure = RiskWarningConfigure.list()?.get(0)
        def valueRiseWeight = riskWarningConfigure.valueRiseWeight
        // 价值上升波动范围
        def valueDeclineWeight = -riskWarningConfigure.valueDeclineWeight
        // 价值下降波动范围
        batchEstateList?.each {
            def estate = it?.estate
            def priceFluctuationsRatio = estate?.priceFluctuationsRatio
            if (priceFluctuationsRatio && priceFluctuationsRatio >= valueRiseWeight)
            {
                highRiskUpCounts++
                highRiskCounts++
            }
            if (priceFluctuationsRatio && priceFluctuationsRatio <= valueDeclineWeight)
            {
                highRiskDownCounts++
                highRiskCounts++
            }
            def firstEvaluatePrice = estate?.firstEvaluatePrice ?: 0
            def latestEvaluatePrice = estate?.latestEvaluatePrice ?: 0
            def loansbalance = estate?.loansbalance ?: 0
            if (latestEvaluatePrice - firstEvaluatePrice - loansbalance > 0)
            {
                netValue++
            }
            estateList.add(estate)
        }

        println "highRiskCounts:" + highRiskCounts
        println "highRiskUpCounts:" + highRiskUpCounts
        println "highRiskDownCounts:" + highRiskDownCounts
        println "netValue:" + netValue
        println "counts:" + counts

        def estateIdSql = ""
        if (estateList)
        {
            String estateIdList = estateList*.id?.toString()
            estateIdSql += " (" + estateIdList?.substring(1, estateIdList.lastIndexOf("]")) + ")"
        }

        // 排序
        estateList = BatchEstate.executeQuery("select b.estate from BatchEstate b where b.batch.id = ${estateImportBatch?.id} order by abs(b.estate.priceFluctuationsRatio) desc", [max: 20])

        def user = User.findById(springSecurityService.principal.id)
        def accountName = user?.account?.name
        def defaultCity = user?.account?.city?.zoneNumber
        def zoneNumber = params["city"] ?: defaultCity
        def gtype = params['gtype']

        def list = []

        // 押品剩余还款期分布
        def db = new groovy.sql.Sql(dataSource)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        def estateStatusId = EstateStatusDict.findByCode('1')?.id
        def sql3 = "select '【0-5】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 0 and remain_time <=60 UNION "
        sql3 += "select '【5-10】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 60 and remain_time <= 120 UNION "
        sql3 += "select '【10-15】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 120 and remain_time <=180 UNION "
        sql3 += "select '【15-20】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 180 and remain_time <=240 UNION "
        sql3 += "select '【20年以上】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and id in " + estateIdSql + " and remain_time > 240 ORDER BY counts desc"
        def remainTimeList = db.rows(sql3).asList()
        db.close()

        // 押品剩余还款期分布
        def sql4 = "select new map(count(re.id) as overnumbers,"
        sql4 += " (select communityName from Estate where id = re.mortgage.id) as communityName, "
        sql4 += " (select communityId from Estate where id = re.mortgage.id) as communityId, "
        sql4 += " (select account.name from Estate where id = re.mortgage.id) as accountName, "
        sql4 += " (select externalId from Mortgage where id = re.mortgage.id) as externalId, "
        sql4 += " re.mortgage.id as mortgageId)"
        sql4 += " FROM RepaymentImport re LEFT JOIN re.mortgage m LEFT JOIN m.collateral.estate e1"
        sql4 += " WHERE re.continuousOverdueNumber > 0 AND re.overdueState.code = 'NoPay'"
        sql4 += " and e1.id in " + estateIdSql
        sql4 += " group by re.mortgage ORDER BY overnumbers desc"

        println sql4

        // String ids = ""
        def overdueCounts = 0
        def overdueList = Estate.executeQuery(sql4).asList()
        if (overdueList?.size() > 0)
        {
            overdueList?.each {
                overdueCounts += it?.overnumbers
                // ids += it?.communityId + ","
            }
            // ids = ids?.substring(0, ids?.lastIndexOf(","))
        }

        // def standardPrice = apiService.standardPrice(zoneNumber, ids)
        // def standardPriceList = standardPrice['data']
        // for (def i = 0; i < overdueList?.size(); i++)
        // {
        //     for (def j = 0; j < standardPriceList?.size(); j++)
        //     {
        //         if (overdueList[i]?.communityId?.toInteger() == standardPriceList[j]?.id?.toInteger())
        //         {
        //             overdueList[i].standardPrice = standardPriceList[j].standardPrice
        //         }
        //     }
        // }

        println "list: " + list

        // respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view:'report'
        // if (gtype == "999")
        // {
        // respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice, currentTotalPrice: currentTotalPrice], view: 'reportPrinting'
        // }
        // else
        // {
        respond list, model: [estateImportBatch: estateImportBatch, netValue: netValue, highRiskCounts: highRiskCounts, highRiskUpCounts: highRiskUpCounts, highRiskDownCounts: highRiskDownCounts, counts: counts, estateList: estateList, batchEstateList: batchEstateList, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts], view: 'batchReport'
        // }
    }

    // 分析报告--贷款
    def report1(Integer max)
    {

        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        params.offset = params.offset ? params.offset.toInteger() : 0

        def user = User.findById(springSecurityService.principal.id)
        def accountName = user?.account?.name
        def defaultCity = user?.account?.city?.zoneNumber
        def zoneNumber = params["city"] ?: defaultCity

        def sql1 = "select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName) from Estate e where e.estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.zoneNumber = '${zoneNumber}' group by e.district.id order by counts desc"
        def estateList = Estate.executeQuery(sql1).asList()
        def districtCounts = estateList?.size()
        def estateCounts = 0
        estateList?.each {
            if (it?.counts && it?.counts > 0)
            {
                estateCounts += it?.counts
            }
        }

        def districtName1 = estateList[0]?.districtName
        def districtName2 = estateList[1]?.districtName
        def districtName3 = estateList[2]?.districtName
        def districtEstateCounts1 = estateList[0]?.counts ? estateList[0]?.counts : 0
        def districtEstateCounts2 = estateList[1]?.counts ? estateList[1]?.counts : 0
        def districtEstateCounts3 = estateList[2]?.counts ? estateList[2]?.counts : 0

        def list = []

        // 押品剩余还款期分布
        def db = new groovy.sql.Sql(dataSource)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        def cityId = City.findByZoneNumber(zoneNumber)?.id
        def estateStatusId = EstateStatusDict.findByName('在押')?.id
        def sql3 = "select '【0-5】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 0 and remain_time <=60 UNION "
        sql3 += "select '【5-10】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 60 and remain_time <= 120 UNION "
        sql3 += "select '【10-15】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 120 and remain_time <=180 UNION "
        sql3 += "select '【15-20】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 180 and remain_time <=240 UNION "
        sql3 += "select '【20年以上】' as remaintime, count(id) as counts from estate where estate_status_id = '${estateStatusId}' and account_id in " + accountSql + " and city_id = '${cityId}' and remain_time > 240 ORDER BY counts desc"
        def remainTimeList = db.rows(sql3).asList()
        db.close()

        // 押品剩余还款期分布
        def sql4 = "select new map(count(re.id) as overnumbers,"
        sql4 += " (select communityName from Estate where id = (select estate.id from Mortgage where id = re.mortgage.id)) as communityName, "
        sql4 += " (select communityId from Estate where id = (select estate.id from Mortgage where id = re.mortgage.id)) as communityId, "
        sql4 += " (select account.name from Estate where id = (select estate.id from Mortgage where id = re.mortgage.id)) as accountName, "
        sql4 += " (select externalId from Mortgage where id = re.mortgage.id) as externalId, "
        sql4 += " re.mortgage.id as mortgageId)"
        sql4 += " FROM RepaymentImport re LEFT JOIN re.mortgage m LEFT JOIN m.estate e1"
        sql4 += " WHERE re.continuousOverdueNumber > 0 AND re.overdueState.code = 'NoPay'"
        sql4 += " and e1.city.zoneNumber ='${zoneNumber}'"
        sql4 += " group by re.mortgage ORDER BY overnumbers desc"

        String ids = ""
        def overdueCounts = 0
        def overdueList = Estate.executeQuery(sql4).asList()
        if (overdueList?.size() > 0)
        {
            overdueList?.each {
                overdueCounts += it?.overnumbers
                ids += it?.communityId + ","
            }
            ids = ids?.substring(0, ids?.lastIndexOf(","))
        }

        def standardPrice = apiService.standardPrice(zoneNumber, ids)
        def standardPriceList = standardPrice['data']
        for (
            def i = 0;
                i < overdueList?.size();
                i++)
        {
            for (
                def j = 0;
                    j < standardPriceList?.size();
                    j++)
            {
                if (overdueList[i]?.communityId?.toInteger() == standardPriceList[j]?.id?.toInteger())
                {
                    overdueList[i].standardPrice = standardPriceList[j].standardPrice
                }
            }
        }

        def cityName = Area.findByZoneNumber(zoneNumber)?.name
        println "cityName: " + cityName
        println "zoneNumber: " + zoneNumber
        println "accountName: " + accountName
        println "districtCounts: " + districtCounts
        println "estateCounts: " + estateCounts
        println "districtName1: " + districtName1
        println "districtName2: " + districtName2
        println "districtName3: " + districtName3
        println "districtEstateCounts1: " + districtEstateCounts1
        println "districtEstateCounts2: " + districtEstateCounts2
        println "districtEstateCounts3: " + districtEstateCounts3
        println "remainTimeList: " + remainTimeList
        println "overdueList: " + overdueList
        println "overdueCounts: " + overdueCounts
        println "list: " + list

        respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1, districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2, districtEstateCounts3: districtEstateCounts3, remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts,], view: 'report1'
    }

    def estateStartAnalyze()
    {
        def user = User.findById(springSecurityService.principal.id)
        def defaultCity = user?.account?.city?.zoneNumber
        def zoneNumber = params["city"] ?: defaultCity
        def cityId = Area.findByZoneNumber(zoneNumber)?.id

        SimpleDateFormat format = new SimpleDateFormat("yyyy")
        Calendar c = Calendar.getInstance()
        def yearsList = []
        for (
            i in
                0..9)
        {
            c = Calendar.getInstance()
            c.add(Calendar.YEAR, i - 9);
            yearsList.add(format.format(c.getTime()))
        }

        def sql = "select new map(to_char(e.startDate, 'yyyy') as startDate, count(e.id) as counts) from Estate e where e.estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.id = '${cityId}' group by to_char(e.startDate, 'yyyy') ORDER BY counts desc"
        def result = Estate.executeQuery(sql).asList()

        def estateCounts = 0
        result?.each {
            estateCounts += it?.counts
        }

        def resultList = []
        for (
            def i = 0;
                i < yearsList?.size();
                i++)
        {
            def resultMap = [counts: 0, percent: 0, startDate: yearsList[i]]
            for (
                def j = 0;
                    j < result?.size();
                    j++)
            {
                if (yearsList[i] == result[j]?.startDate)
                {
                    resultMap.counts = result[j].counts
                    resultMap.percent = result[j].counts / estateCounts
                }
            }
            resultList.add(resultMap)
        }

        render([result: resultList, y1: result[0]?.startDate, y2: result[1]?.startDate, y3: result[2]?.startDate] as JSON)
    }

    // top5小区价格分布
    def top5CommunityPriceTrend()
    {
        def user = User.findById(springSecurityService.principal.id)
        def defaultCity = user?.account?.city?.zoneNumber
        def zoneNumber = params["city"] ?: defaultCity
        if (!zoneNumber)
        {
            render([result: []] as JSON)
            return
        }
        def top5PriceTrend = apiService.top5PriceTrend(zoneNumber)
        render([result: top5PriceTrend['data']] as JSON)
    }

    // 全量押品分析报告
    def fullQuantityReport(Integer max)
    {
        long start = System.currentTimeMillis()
        params.max = Math.min(params.max ? params.max.toInteger() : 10, 100)
        params.offset = params.offset ? params.offset.toInteger() : 0
        def houseNature = params["houseNature"]
        def buildType = params["buildType"]
        def gtype = params["gtype"]
        println "houseNature:" + houseNature
        println "buildType:" + buildType

        def ghouseNatureId
        def gbuildTypeId

        def gsql1 = ""
        def gsql2 = ""

        if (houseNature == "new")
        {
            ghouseNatureId = HouseNatureDict.findByName("新房").id
        }
        else if (houseNature == "sec")
        {
            ghouseNatureId = HouseNatureDict.findByName("二手房").id
        }

        if (buildType == "zhuzhai")
        {
            gbuildTypeId = BuildingTypeDict.findByName("住宅").id
        }
        else if (buildType == "shangye")
        {
            gbuildTypeId = BuildingTypeDict.findByName("商业").id
        }
        else if (buildType == "gongye")
        {
            gbuildTypeId = BuildingTypeDict.findByName("工业").id
        }
        else if (buildType == "bangong")
        {
            gbuildTypeId = BuildingTypeDict.findByName("办公").id
        }

        if (ghouseNatureId)
        {
            gsql1 = gsql1 + " and e.houseNature.id = '" + ghouseNatureId + "' ";
            gsql2 = gsql2 + " and e.house_nature_id = '" + ghouseNatureId + "' ";
        }
        if (gbuildTypeId)
        {
            gsql1 = gsql1 + " and e.buildingType.id = '" + gbuildTypeId + "' ";
            gsql2 = gsql2 + " and e.building_type_id = '" + gbuildTypeId + "' ";
        }

        def user = User.findById(springSecurityService.principal.id)
        def accountName = user?.account?.name
        def zoneNumber = '410100'

        def sql1 = "select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName) from Estate e where e.estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.zoneNumber = '${zoneNumber}' ${gsql1}  group by e.district.id order by counts desc"
        def estateList = Estate.executeQuery(sql1).asList()
        def districtCounts = estateList?.size()
        def estateCounts = 0
        estateList?.each {
            if (it?.counts && it?.counts > 0)
            {
                estateCounts += it?.counts
            }
        }

        def districtName1 = estateList[0]?.districtName
        def districtName2 = estateList[1]?.districtName
        def districtName3 = estateList[2]?.districtName
        def districtEstateCounts1 = estateList[0]?.counts ? estateList[0]?.counts : 0
        def districtEstateCounts2 = estateList[1]?.counts ? estateList[1]?.counts : 0
        def districtEstateCounts3 = estateList[2]?.counts ? estateList[2]?.counts : 0

        def list = []

        def sql2 = "select new map(count(e.id) as counts, e.communityId as communityId, e.communityName as community, (select a.name from Area a where id = e.district.id) as district, (select b.name from Area b where id = e.city.id) as city) from Estate e where estateStatus.code = '1' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.city.zoneNumber = '${zoneNumber}' ${gsql1} group by e.communityId, e.communityName, e.district.id, e.city.id ORDER BY counts desc, communityName"
        def estateList2 = Estate.executeQuery(sql2).asList()
        def communityCounts = estateList2?.size()

        def c1 = estateList2[0]?.community
        def d1 = estateList2[0]?.district
        def t1 = estateList2[0]?.counts ? estateList2[0]?.counts : 0

        def c2 = estateList2[1]?.community
        def d2 = estateList2[1]?.district
        def t2 = estateList2[1]?.counts ? estateList2[1]?.counts : 0

        def top20CommunityList = []
        for (
            def i = 0;
                i < 20;
                i++)
        {
            if (estateList2[i])
            {
                top20CommunityList.add(estateList2[i])
            }
        }

        // 押品剩余还款期分布
        def db = new groovy.sql.Sql(dataSource)
        def accountSql = Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
        def cityId = City.findByZoneNumber(zoneNumber)?.id
        def estateStatusId = EstateStatusDict.findByCode('1')?.id
        def sql3 = "select '【0-5】' as remaintime, count(e.id) as counts from estate e where e.estate_status_id = '${estateStatusId}' and e.account_id in " + accountSql + " and e.city_id = '${cityId}' " + gsql2 + " and e.remain_time > 0 and e.remain_time <=60 UNION "
        sql3 += "select '【5-10】' as remaintime, count(e.id) as counts from estate e where e.estate_status_id = '${estateStatusId}' and e.account_id in " + accountSql + " and e.city_id = '${cityId}'  " + gsql2 + " and e.remain_time > 60 and e.remain_time <= 120 UNION "
        sql3 += "select '【10-15】' as remaintime, count(e.id) as counts from estate e where e.estate_status_id = '${estateStatusId}' and e.account_id in " + accountSql + " and e.city_id = '${cityId}'  " + gsql2 + " and e.remain_time > 120 and e.remain_time <=180 UNION "
        sql3 += "select '【15-20】' as remaintime, count(e.id) as counts from estate e where e.estate_status_id = '${estateStatusId}' and e.account_id in " + accountSql + " and e.city_id = '${cityId}'  " + gsql2 + " and e.remain_time > 180 and e.remain_time <=240 UNION "
        sql3 += "select '【20年以上】' as remaintime, count(e.id) as counts from estate e where e.estate_status_id = '${estateStatusId}' and e.account_id in " + accountSql + " and e.city_id = '${cityId}'  " + gsql2 + " and e.remain_time > 240 ORDER BY counts desc"
        def remainTimeList = db.rows(sql3).asList()
        db.close()

        // 押品剩余还款期分布
        def sql4 = "select new map(count(re.id) as overnumbers,"
        sql4 += " (select communityName from Estate where id = re.mortgage.id) as communityName, "
        sql4 += " (select communityId from Estate where id = re.mortgage.id) as communityId, "
        sql4 += " (select account.name from Estate where id = re.mortgage.id) as accountName, "
        sql4 += " (select externalId from Mortgage where id = re.mortgage.id) as externalId, "
        sql4 += " re.mortgage.id as mortgageId)"
        sql4 += " FROM RepaymentImport re LEFT JOIN re.mortgage m LEFT JOIN m.collateral.estate e1"
        sql4 += " WHERE re.continuousOverdueNumber > 0 AND re.overdueState.code = 'NoPay'"
        sql4 += " and e1.city.zoneNumber ='${zoneNumber}'"
        sql4 += " group by re.mortgage ORDER BY overnumbers desc"

        String ids = ""
        def overdueCounts = 0
        def overdueList = Estate.executeQuery(sql4).asList()
        if (overdueList?.size() > 0)
        {
            overdueList?.each {
                overdueCounts += it?.overnumbers
                ids += it?.communityId + ","
            }
            ids = ids?.substring(0, ids?.lastIndexOf(","))
        }

        def standardPrice = apiService.standardPrice(zoneNumber, ids)
        def standardPriceList = standardPrice['data']
        for (
            def i = 0;
                i < overdueList?.size();
                i++)
        {
            for (
                def j = 0;
                    j < standardPriceList?.size();
                    j++)
            {
                if (overdueList[i]?.communityId?.toInteger() == standardPriceList[j]?.id?.toInteger())
                {
                    overdueList[i].standardPrice = standardPriceList[j].standardPrice
                }
            }
        }

        // 押品重估价值
        def sql5 = "select new map(to_char(m.startDate, 'yyyy') as startDate, SUM(m.loansEvaluateTotalPrice)/100000000 as loanTotalPrice, sum(m.totalPrice)/100000000 as currentTotalPrice, sum(m.totalPrice - m.loansEvaluateTotalPrice) / SUM(m.loansEvaluateTotalPrice) as amplitude)"
        sql5 += " from Mortgage m left join m.collateral.estate e "
        sql5 += " where e.estateStatus.code = '1' and e.city.zoneNumber = '${zoneNumber}' and e.account.id in " + accountSql + gsql1
        sql5 += " GROUP BY to_char(m.startDate, 'yyyy') ORDER BY startDate desc"


        def loanTotalPrice = 0
        def currentTotalPrice = 0
        def evaluateList = Estate.executeQuery(sql5).asList()
        println "evaluateList:" + evaluateList
        evaluateList?.each {
            if (it?.loanTotalPrice && it?.loanTotalPrice > 0)
            {
                loanTotalPrice += it?.loanTotalPrice
            }
            if (it?.currentTotalPrice && it?.currentTotalPrice > 0)
            {
                currentTotalPrice += it?.currentTotalPrice
            }
        }
        if (!evaluateList[0]?.startDate)
        {
            evaluateList = []
        }
        //统计数据
        //新房和二手房
        def xinfangId = HouseNatureDict.findByName("新房").id
        def ershoufangId = HouseNatureDict.findByName("二手房").id
        def statusId = EstateStatusDict.findByName("在押").id
        def sqlnew = "select  b.name as name,count(1) as counts from estate  e " + " left join area a on e.city_id = a.area_id " + " left join area b on e.district_id = b.area_id  " + " where e.city_id = ${cityId} " + " and e.house_nature_id = ${xinfangId}  " + " and e.account_id in ${accountSql}  " + " and e.estate_status_id = ${statusId} ${gsql2}" + " group by b.name " + " order by counts desc limit 1 offset 0 "

        def sqlSecond = "select  b.name as name,count(1) as counts from estate  e " + " left join area a on e.city_id = a.area_id " + " left join area b on e.district_id = b.area_id  " + " where e.city_id = ${cityId} " + " and e.house_nature_id = ${ershoufangId}  " + " and e.account_id in ${accountSql}  " + " and e.estate_status_id = ${statusId} ${gsql2} " + " group by b.name " + " order by counts desc limit 1 offset 0 "
        def newHouse = db.rows(sqlnew).asList()

        def secHouse = db.rows(sqlSecond).asList()


        def newCountsql = "select count(1) from Estate e where e.houseNature.name = '新房' and e.city.id = ${cityId} and e.estateStatus.name = '在押' and e.account.id in ${accountSql} ${gsql1} "
        def newCount = Estate.executeQuery(newCountsql).asList();
        def secCountsql = "select count(1) from Estate e where e.houseNature.name = '二手房' and e.city.id = ${cityId} and e.estateStatus.name = '在押' and e.account.id in ${accountSql} ${gsql1}"
        def secCount = Estate.executeQuery(secCountsql).asList();

        //用途种类
        def zhuzhaiId = BuildingTypeDict.findByName('住宅').id
        def shangyeId = BuildingTypeDict.findByName('商业').id
        def bangongId = BuildingTypeDict.findByName('办公').id
        def gongyeId = BuildingTypeDict.findByName('工业').id
        def buildTypesql = "select '住宅' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}   and e.building_type_id = ${zhuzhaiId} and e.account_id in" + accountSql + " " + gsql2 + " union " + "select '商业' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.building_type_id = ${shangyeId}  and e.account_id in" + accountSql + " " + gsql2 + " union " + "select '办公' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.building_type_id = ${bangongId} and e.account_id in" + accountSql + "  " + gsql2 + " union " + "select '工业' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.building_type_id = ${gongyeId}  and e.account_id in" + accountSql + gsql2 + " order by name"
        def yongtuzhonglei = db.rows(buildTypesql).asList()

        def zuhedaikuanId = LoanPropertiesDict.findByName("组合贷款").id
        def gongjijindaikuanId = LoanPropertiesDict.findByName("住房公积金贷款").id
        def shangyedaikuanId = LoanPropertiesDict.findByName("商业贷款").id
        //贷款性质
        def sql30 = "select '组合贷款' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}   and e.loan_properties_id = ${zuhedaikuanId} and e.account_id in" + accountSql + " " + gsql2 + " union " + "select '住房公积金贷款' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.loan_properties_id = ${gongjijindaikuanId}  and e.account_id in" + accountSql + " " + gsql2 + " union " + "select '商业贷款' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.loan_properties_id = ${shangyedaikuanId} and e.account_id in" + accountSql + gsql2 + "order by name"
        def daikuanxingzhi = db.rows(sql30).asList()

        def zuigaoId = LoanRightsTypeDict.findByName('最高额抵押').id
        def yibanId = LoanRightsTypeDict.findByName('一般抵押').id
        def diyaId = LoanRightsTypeDict.findByName('抵押').id
        def dianquanId = LoanRightsTypeDict.findByName('典权').id
        //权利种类
        def sql40 = "select '最高额抵押' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}   and e.loan_rights_type_id = ${zuigaoId} and e.account_id in" + accountSql + " " + gsql2 + " union " + " select '一般抵押' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.loan_rights_type_id = ${yibanId}  and e.account_id in" + accountSql + " " + gsql2 + "  union " + " select '抵押' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.loan_rights_type_id = ${diyaId} and e.account_id in" + accountSql + " " + gsql2 + " union " + " select '典权' as name, count(1) as counts  from estate  e where e.city_id = ${cityId} and e.estate_Status_id = ${statusId}  and e.loan_rights_type_id = ${dianquanId} and e.account_id in" + accountSql + gsql2 + " order by name"

        def quanlizhonglei = db.rows(sql40).asList()


        //新房线
        def sql50 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (" + " select name from area  where parent_area = ${cityId} order by name) t " + " left join(select  b.name as name, count(1) as counts from estate  e  " + " left join area b on e.district_id = b.area_id  " + " where e.city_id = ${cityId}  and e.house_nature_id = ${xinfangId}  " + " and e.account_id in " + accountSql + " and e.estate_status_id = ${statusId} " + gsql2 + " group by b.name   order by b.name) t1 on t.name = t1.name"
        def xinfangxian = db.rows(sql50).asList()
        def xinfangxianData = ""
        xinfangxian.each {
            xinfangxianData = xinfangxianData + it?.counts + ","
        }
        xinfangxianData = xinfangxianData.substring(0, xinfangxianData.lastIndexOf(","))
        //二手房线
        def sql60 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (" + " select name from area  where parent_area = 1660 order by name) t " + " left join(select  b.name as name, count(1) as counts from estate  e  " + " left join area b on e.district_id = b.area_id  " + " where e.city_id = 1660  and e.house_nature_id = ${ershoufangId} " + " and e.account_id in " + accountSql + " and e.estate_status_id = ${statusId} " + gsql2 + " group by b.name   order by b.name) t1 on t.name = t1.name"
        def ershoufangxian = db.rows(sql60).asList()

        def ershoufangxianData = ""
        ershoufangxian.each {
            ershoufangxianData = ershoufangxianData + it?.counts + ","
        }
        ershoufangxianData = ershoufangxianData.substring(0, ershoufangxianData.lastIndexOf(","))

        //房屋用途--住宅  4个类型
        def sql70 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (select name from area  where parent_area = ${cityId} order by name) t " + "left join(select  b.name as name, count(1) as counts from estate  e " + " left join area b on e.district_id = b.area_id " + " where e.city_id = ${cityId} " + " and e.building_type_id = ${zhuzhaiId} " + " and e.account_id in  " + accountSql + gsql2 + " group by b.name  " + " order by b.name) t1 on t.name = t1.name "
        def zhuzhai = db.rows(sql70).asList()
        def zhuzhaiData = ""
        zhuzhai.each {
            zhuzhaiData = zhuzhaiData + it.counts + ","
        }
        zhuzhaiData = zhuzhaiData.substring(0, zhuzhaiData.lastIndexOf(","))

        //房屋用途--商业
        def sql71 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (select name from area  where parent_area = ${cityId} order by name) t " + "left join(select  b.name as name, count(1) as counts from estate  e " + " left join area b on e.district_id = b.area_id " + " where e.city_id = ${cityId} " + " and e.building_type_id = ${shangyeId} " + " and e.account_id in  " + accountSql + gsql2 + " group by b.name  " + " order by b.name) t1 on t.name = t1.name; "
        def shangye = db.rows(sql71).asList()
        def shangyeData = ""
        shangye.each {
            shangyeData = shangyeData + it.counts + ","
        }
        shangyeData = shangyeData.substring(0, shangyeData.lastIndexOf(","))

        //房屋用途--办公
        def sql72 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (select name from area  where parent_area = ${cityId} order by name) t " + "left join(select  b.name as name, count(1) as counts from estate  e " + " left join area b on e.district_id = b.area_id " + " where e.city_id = ${cityId} " + " and e.building_type_id = ${bangongId} " + " and e.account_id in " + accountSql + gsql2 + " group by b.name  " + " order by b.name) t1 on t.name = t1.name; "
        def bangong = db.rows(sql72).asList()
        def bangongData = ""
        bangong.each {
            bangongData = bangongData + it.counts + ","
        }
        bangongData = bangongData.substring(0, bangongData.lastIndexOf(","))

        //房屋用途--工业
        def sql73 = "select t.name, CASE when t1.counts IS NULL  then  0 else t1.counts End from (select name from area  where parent_area = ${cityId} order by name) t " + "left join(select  b.name as name, count(1) as counts from estate  e " + " left join area b on e.district_id = b.area_id " + " where e.city_id = ${cityId} " + " and e.building_type_id = ${gongyeId} " + " and e.account_id in " + accountSql + gsql2 + " group by b.name  " + " order by b.name) t1 on t.name = t1.name; "
        def gongye = db.rows(sql73).asList()
        def districtX = ""

        def gongyeData = ""
        gongye.each {
            districtX = districtX + it.name + ","
            gongyeData = gongyeData + it.counts + ","
        }
        districtX = districtX.substring(0, districtX.lastIndexOf(","))
        gongyeData = gongyeData.substring(0, gongyeData.lastIndexOf(","))


        //  权利种类数量及占比 一个类型一个sql语句 4个类型
        //公用日期
        def recentData = getRecentDate()

        def sql80 = "select  to_char(e.start_date, 'yyyy-MM') as startDate,count(1) as counts  from estate  e " + " where e.loan_rights_type_id = ${zuigaoId} " + " and e.city_id = ${cityId} " + " and e.account_id in ${accountSql}  ${gsql2} " + " group by  startDate " + " order by startDate desc " + " limit 12 offset 0 ;"
        def zuigaoe = db.rows(sql80).asList()
        def zuigaoedata = dealData(zuigaoe)

        def sql81 = "select  to_char(e.start_date, 'yyyy-MM') as startDate,count(1) as counts  from estate  e " + " where e.loan_rights_type_id = ${yibanId} " + " and e.city_id = ${cityId} " + " and e.account_id in ${accountSql} ${gsql2} " + " group by  startDate " + " order by startDate desc " + " limit 12 offset 0 ;"
        def yiban = db.rows(sql81).asList()
        def yibandata = dealData(yiban)

        def sql82 = "select  to_char(e.start_date, 'yyyy-MM') as startDate,count(1) as counts  from estate  e " + " where e.loan_rights_type_id = ${diyaId} " + " and e.city_id = ${cityId} " + " and e.account_id in ${accountSql}  " + gsql2 + " group by  startDate " + " order by startDate desc " + " limit 12 offset 0 ;"
        def diya = db.rows(sql82).asList()
        def diyadata = dealData(diya)

        def sql83 = "select  to_char(e.start_date, 'yyyy-MM') as startDate,count(1) as counts  from estate  e " + " where e.loan_rights_type_id = ${dianquanId} " + " and e.city_id = ${cityId} " + " and e.account_id in ${accountSql}  " + gsql2 + " group by  startDate " + " order by startDate desc " + " limit 12 offset 0 ;"
        def dianquan = db.rows(sql83).asList()
        def dianquandata = dealData(dianquan)

        def cityName = Area.findByZoneNumber(zoneNumber)?.name

        long end = System.currentTimeMillis()

        if (gtype == "999")
        {
            respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1,
                districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2,
                districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList,
                remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice,
                currentTotalPrice: currentTotalPrice, newHouse: newHouse[0], secHouse: secHouse[0], yongtuzhonglei: yongtuzhonglei, daikuanxingzhi: daikuanxingzhi, quanlizhonglei: quanlizhonglei,
                newCount: newCount[0], secCount: secCount[0], zhuzhaiData: zhuzhaiData, shangyeData: shangyeData, bangongData: bangongData, gongyeData: gongyeData, districtX: districtX, xinfangxianData: xinfangxianData,
                ershoufangxianData: ershoufangxianData, recentData: recentData, zuigaoedata: zuigaoedata, yibandata: yibandata, diyadata: diyadata, dianquandata: dianquandata, houseNature: houseNature, buildType: buildType], view: 'fullQuantityReportPrinting'
        }
        else
        {
            respond list, model: [cityName: cityName, zoneNumber: zoneNumber, accountName: accountName, districtCounts: districtCounts, estateCounts: estateCounts, districtName1: districtName1,
                districtName2: districtName2, districtName3: districtName3, districtEstateCounts1: districtEstateCounts1, districtEstateCounts2: districtEstateCounts2,
                districtEstateCounts3: districtEstateCounts3, communityCounts: communityCounts, c1: c1, c2: c2, d1: d1, d2: d2, t1: t1, t2: t2, top20CommunityList: top20CommunityList,
                remainTimeList: remainTimeList, overdueList: overdueList, overdueCounts: overdueCounts, evaluateList: evaluateList, loanTotalPrice: loanTotalPrice,
                currentTotalPrice: currentTotalPrice, newHouse: newHouse[0], secHouse: secHouse[0], yongtuzhonglei: yongtuzhonglei, daikuanxingzhi: daikuanxingzhi, quanlizhonglei: quanlizhonglei,
                newCount: newCount[0], secCount: secCount[0], zhuzhaiData: zhuzhaiData, shangyeData: shangyeData, bangongData: bangongData, gongyeData: gongyeData, districtX: districtX, xinfangxianData: xinfangxianData,
                ershoufangxianData: ershoufangxianData, recentData: recentData, zuigaoedata: zuigaoedata, yibandata: yibandata, diyadata: diyadata, dianquandata: dianquandata, houseNature: houseNature, buildType: buildType], view: 'fullQuantityReport'
        }

    }

    /**
     * 获取12个月份字符串
     * @return
     */
    def getRecentDate()
    {
        StringBuffer rStr = new StringBuffer("");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM")
        Calendar ca = Calendar.getInstance()
        for (
            int i = -12;
                i <= -1;
                i++)
        {
            ca.setTime(new Date())
            ca.add(Calendar.MONTH, i)
            rStr.append(sdf.format(ca.getTime()) + ",")
        }
        return rStr.toString().substring(0, rStr.toString().lastIndexOf(","))
    }

    def dealData(def list)
    {
        Map<String, String> map = new HashMap<String, String>()
        list.each {
            map.put(it?.startdate, it?.counts)
            //            println it?.startdate+"==="+it?.counts
        }
        StringBuffer rStr = new StringBuffer("")
        def recentDate = getRecentDate().split(",")
        int y = -1
        recentDate.each {
            String temp = ","
            y++
            Object obj = map.get(it)
            if (y == 11)
            {
                temp = ""
            }
            if (obj)
            {
                rStr.append(obj + temp)
            }
            else
            {
                rStr.append("0" + temp)
            }
        }
        // println "Final:"+rStr
        return rStr
    }
}